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?

• Martin Smith: Into a #temp table that already exists or it would need to create a new one?
• scapegoat17: @MartinSmith - It would be a new one.
• gnud: LIKE ISNULL('%MORESTUFF%', '%') will always be the same as LIKE '%MORESTUFF%', won't it? Since '%MORESTUFF%' (the string literal) is never null?

Answer 1

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%',
 '%')

Answer 2

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

• godfathr: Interesting. I just had the same issue. Adding "As [x]" at the end made everything work just fine. Why is this?
• wootscootinboogie: @godfathr it's because the from clause is using a derived table

Answer 3

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.

• FrenkyB: Is it possible to add columns to #TempTableName?
• Comfortably Numb: @FrenkyB yes, once table is created you can use ALTER TABLE ADD COLUMN statement
• user2948533: Correct, I was getting temptable already created error, we dont need to create separately, it creates on the fly!decent!
• OfirD: As detailed in @Shaun Luttin answer, the FROM clause needs an alias at its end, else an error happens.

Answer 4

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

• Matt Kemp: Extra drop table stuff is handy

Answer 5

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.

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

Answer 7

SELECT * INTO #TempTable 
FROM SampleTable
WHERE...
SELECT * FROM #TempTable
DROP TABLE #TempTable

Answer 8

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;

Answer 9

use as at end of query

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