CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008select
Dinesh Reddy Alla
Dinesh Reddy Alla

Reputation: 1717

How to insert multiple select statements into a temp table

I am having three tables with different data and i need to insert into one TEMP table and return that table in StoredProcedure.

I tried as:

-- To get last 10 Days Letters count
SELECT col1,col2,1 AS Type, LettersCount
INTO #temp FROM tblData

-- To get last 4 weeks Letters count
SELECT col1,col2,2 AS Type, LettersCount
INTO #temp FROM tblData

-- To get month wise Letters count
SELECT col1,col2,3 AS Type, LettersCount
INTO #temp FROM tblData

Showing Error as

Msg 2714, Level 16, State 1, Line 16
There is already an object named '#temp ' in the database.
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near 'T'.
Msg 2714, Level 16, State 1, Line 32
There is already an object named '#temp ' in the database.

Upvotes: 15

Views: 73433

Answers (5)

Dgan
Dgan

Reputation: 10285

You can Check it Already Exists or NOT

IF OBJECT_ID ('tempdb..#TempLetters') is not null
drop table #TempLetters


SELECT col1,col2,1 AS Type, LettersCount
INTO #TempLetters FROM tblData

-- To get last 4 weeks Letters count
INSERT INTO #TempLetters
SELECT col1,col2,2 AS Type, LettersCount
FROM tblData

-- To get month wise Letters count
INSERT INTO #TempLetters
SELECT col1,col2,3 AS Type, LettersCount
FROM tblData

Upvotes: 31

Hitesh
Hitesh

Reputation: 3498

Why not write just a single insert statement and union the tables before insert

with A as
(
    -- To get last 10 Days Letters count
    SELECT col1,col2,1 AS Type, LettersCount
    FROM tblData
    union all
    -- To get last 4 weeks Letters count
    SELECT col1,col2,2 AS Type, LettersCount
    FROM tblData
    union all
    -- To get month wise Letters count
    SELECT col1,col2,3 AS Type, LettersCount
    FROM tblData
)
select col1, col2, Type, LettersCount
INTO #temp 
FROM A

This will help you add more tables in the select easily if you need as you wont need any more insert statements for them

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93694

The SELECT INTO statement can also be used to create a new, empty table using the schema of another select * into tablename from .. here tablename table should not exist.

Change your insert like this: 

SELECT col1,
       col2,
       1 AS Type,
       LettersCount
INTO   #temp
FROM   tblData

-- To get last 4 weeks Letters count
INSERT INTO #temp
SELECT col1,col2,2 AS Type,LettersCount
FROM   tblData

-- To get month wise Letters count
INSERT INTO #temp
SELECT col1,col2,3 AS Type,LettersCount
FROM   tblData

Upvotes: 5

Jonathan Leffler
Jonathan Leffler

Reputation: 753605

Create the temporary table once, then insert into it for the other two SELECT statements:

SELECT col1, col2, 1 AS Type, LettersCount
  INTO #temp
  FROM tblData;

INSERT INTO #temp
    SELECT col1, col2, 2 AS Type, LettersCount
      FROM tblData;

INSERT INTO #temp
    SELECT col1, col2, 3 AS Type, LettersCount
      FROM tblData;

Upvotes: 9

Eduard Uta
Eduard Uta

Reputation: 2607

The error occurs because the first select into statement creates the table and the second and third tries to recreate it again.

Change the second and third queries into:

insert into #temp
select..

Upvotes: 1

Related Questions