How to insert multiple select statements in a temporary table

Question

I have three tables with different data, and I need to insert one TEMP table and return this table to StoredProcedure.

I tried like:

-- 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 

Error display 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. 

Answer 1

You can check that 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 

Answer 2

The SELECT INTO can also be used to create a new empty table using the schema of another select * into tablename from .. here the 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 

Answer 3

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

 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; 

Answer 4

The error occurs because the first select statement inserts the table, and the second and third attempts recreate it again.

Change the second and third queries to:

 insert into #temp select.. 

Answer 5

Why not write just one insert statement and join the tables before inserting

 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 easily add more tables to the table if you need, because you no longer need insert instructions for them.