Insert Data Into Temp Table with Query

Question

I have an existing query that outputs current data, and I would like to insert it into a Temp table, but am having some issues doing so. Would anybody have some insight on how to do this?

Here is an example

SELECT *
FROM  (SELECT Received,
              Total,
              Answer,
              ( CASE
                  WHEN application LIKE '%STUFF%' THEN 'MORESTUFF'
                END ) AS application
       FROM   FirstTable
       WHERE  Recieved = 1
              AND application = 'MORESTUFF'
       GROUP  BY CASE
                   WHEN application LIKE '%STUFF%' THEN 'MORESTUFF'
                 END) data
WHERE  application LIKE isNull('%MORESTUFF%', '%')

This seems to output my data currently the way that i need it to, but I would like to pass it into a Temp Table. My problem is that I am pretty new to SQL Queries and have not been able to find a way to do so. Or if it is even possible. If it is not possible, is there a better way to get the data that i am looking for WHERE application LIKE isNull('%MORESTUFF%','%') into a temp table?

Into a `#temp` table that already exists or it would need to create a new one? — Martin Smith, Nov 20 '13 at 21:54
`LIKE ISNULL('%MORESTUFF%', '%')` will always be the same as `LIKE '%MORESTUFF%'`, won't it? Since '%MORESTUFF%' (the string literal) is never null? — gnud, May 28 '18 at 11:53

Yosi Dahari · Accepted Answer · 2013-11-20T22:03:37.980

221

SELECT *
INTO #Temp
FROM

  (SELECT
     Received,
     Total,
     Answer,
     (CASE WHEN application LIKE '%STUFF%' THEN 'MORESTUFF' END) AS application
   FROM
     FirstTable
   WHERE
     Recieved = 1 AND
     application = 'MORESTUFF'
   GROUP BY
     CASE WHEN application LIKE '%STUFF%' THEN 'MORESTUFF' END) data
WHERE
  application LIKE
    isNull(
      '%MORESTUFF%',
      '%')

edited Nov 20 '13 at 22:03

answered Nov 20 '13 at 21:58

Yosi Dahari

6,794
5
24
44

score 171 · Answer 2 · edited May 28 '18 at 11:44

171

SQL Server R2 2008 needs the AS clause as follows:

SELECT * 
INTO #temp
FROM (
    SELECT col1, col2
    FROM table1
) AS x

The query failed without the AS x at the end.

EDIT

It's also needed when using SS2016, had to add as t to the end.

 Select * into #result from (SELECT * FROM  #temp where [id] = @id) as t //<-- as t

edited May 28 '18 at 11:44

Legends

21,202
16
97
123

answered Apr 29 '15 at 21:50

Shaun Luttin

133,272
81
405
467

5

Interesting. I just had the same issue. Adding "As [x]" at the end made everything work just fine. Why is this? – godfathr May 25 '17 at 22:53
5

@godfathr it's because the from clause is using a derived table – wootscootinboogie Feb 25 '18 at 14:18

score 44 · Answer 3 · answered Nov 20 '13 at 21:57

44

Fastest way to do this is using "SELECT INTO" command e.g.

SELECT * INTO #TempTableName
FROM....

This will create a new table, you don't have to create it in advance.

answered Nov 20 '13 at 21:57

Yuriy Galanter

38,833
15
69
136

Is it possible to add columns to #TempTableName? – FrenkyB Feb 16 '17 at 16:53
@FrenkyB yes, once table is created you can use ALTER TABLE ADD COLUMN statement – Yuriy Galanter Feb 16 '17 at 21:42
Correct, I was getting temptable already created error, we dont need to create separately, it creates on the fly!decent! – user2948533 Jun 02 '21 at 05:22
As detailed in [@Shaun Luttin answer](https://stackoverflow.com/a/29955231/3002584), the `FROM` clause needs an alias at its end, else an error happens. – OfirD Aug 01 '21 at 11:56

theteague · Answer 4 · 2019-08-02T14:14:29.787

18

Personally, I needed a little hand holding figuring out how to use this and it is really, awesome.

IF(OBJECT_ID('tempdb..#TEMP') IS NOT NULL) BEGIN DROP TABLE #TEMP END
        SELECT *
            INTO #TEMP
            FROM (
            The query you want to use many times
            ) AS X

SELECT * FROM #TEMP WHERE THIS = THAT
SELECT * FROM #TEMP WHERE THIS <> THAT
SELECT COL1,COL3 FROM #TEMP WHERE THIS > THAT

DROP TABLE #TEMP

edited Aug 02 '19 at 14:14

answered Jul 20 '17 at 17:59

theteague

413
4
10

1

Extra `drop table` stuff is handy – Matt Kemp Nov 11 '20 at 21:09

score 10 · Answer 5 · answered Nov 20 '13 at 21:57

10

You can do that like this:

INSERT INTO myTable (colum1, column2)
SELECT column1, column2 FROM OtherTable;

Just make sure the columns are matching, both in number as in datatype.

answered Nov 20 '13 at 21:57

wvdz

16,251
4
53
90

score 6 · Answer 6 · edited Nov 30 '16 at 19:10

6

Try this:

SELECT *
INTO #Temp
FROM 
(select * from tblorders where busidate ='2016-11-24' and locationID=12
) as X

Please use alias with x so it will not failed the script and result.

edited Nov 30 '16 at 19:10

Sachith Muhandiram

2,819
10
45
94

answered Nov 30 '16 at 18:42

Alok Sharma

61
1
2

score 4 · Answer 7 · answered Oct 06 '17 at 12:37

4

SELECT * INTO #TempTable 
FROM SampleTable
WHERE...

SELECT * FROM #TempTable
DROP TABLE #TempTable

answered Oct 06 '17 at 12:37

Saqib A. Azhar

994
1
15
27

score 2 · Answer 8 · answered Feb 08 '18 at 11:15

2

This is possible. Try this way:

Create Global Temporary Table 
BossaDoSamba 
On Commit Preserve Rows 
As 
select ArtistName, sum(Songs) As NumberOfSongs 
 from Spotfy 
    where ArtistName = 'BossaDoSamba'
 group by ArtistName;

answered Feb 08 '18 at 11:15

Luiz Lima

101
6

score 0 · Answer 9 · answered Jan 21 '21 at 10:28

0

use as at end of query

Select * into #temp (select * from table1,table2) as temp_table

answered Jan 21 '21 at 10:28

Fayazz B N

117
1
2

Insert Data Into Temp Table with Query

9 Answers9

Linked