How to insert multiple select statements into a temp table

Question

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.

score 30 · Accepted Answer · edited Dec 12 '14 at 07:35

30

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

edited Dec 12 '14 at 07:35

tjeloep

318
1
4
19

answered Dec 12 '14 at 06:45

Dgan

10,077
1
29
51

Thanks! This help me performace my query a lots! – Trương Long May 28 '19 at 07:02

score 8 · Answer 2 · answered Dec 12 '14 at 06:40

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;

Pரதீப் · Answer 3 · 2014-12-12T06:54:57.983

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

score 2 · Answer 4 · answered Dec 12 '14 at 06:55

2

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

answered Dec 12 '14 at 06:55

Hitesh

3,449
8
39
57

In each select statement i used group by and order by. Will it work. – Dinesh Reddy Alla Dec 12 '14 at 06:59
Showing error as Msg 1033, Level 15, State 1, Line 14 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. – Dinesh Reddy Alla Dec 12 '14 at 07:01
order by wont work, group by will, however if you want to do a order by before insert, you can do it after the `From A` line – Hitesh Dec 12 '14 at 07:01
Ok Thank you for your respose. I can use this in some other situation. – Dinesh Reddy Alla Dec 12 '14 at 07:04

score 1 · Answer 5 · answered Dec 12 '14 at 06:38

1

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

answered Dec 12 '14 at 06:38

Eduard Uta

2,477
5
26
36

How to insert multiple select statements into a temp table

5 Answers5