CODEWITHSUNDEEP
sql-server-2017
MartinL
MartinL

Reputation: 45

Select into #TempTable

I have something like the following scenario

Select x, y, z
from mytable
where x = 1

UNION

Select x, y, z
from mytable2
where x = 1

I would like to put the results into a #TempTable and then create a Pivot of the results into #TempTable2

I have tried 

SELECT * INTO #TempTable FROM (
Select x, y, z
from mytable
where x = 1

UNION

Select x, y, z
from mytable2
where x = 1
)

But it get Incorrect syntax near ')' I've forgotten all the other variations I have made but none of them have worked

Upvotes: 0

Views: 128

Answers (1)

gbn
gbn

Reputation: 432271

Add an alias to the derived table. Here I use X because I'm imaginative

SELECT * 
INTO #TempTable 
FROM 
   (
   Select x, y, z
   from mytable
   where x = 1
   UNION
   Select x, y, z
   from mytable2
   where x = 1
   ) AS X

SQL Server needs a reference for the objects in the FROM clause. No alias = no reference
You can see this if we rewrite the query using a CTE

WITH myUnion AS
   (
   Select x, y, z
   from mytable
   where x = 1
   UNION
   Select x, y, z
   from mytable2
   where x = 1
  )
SELECT * 
INTO #TempTable 
FROM myUnion

Upvotes: 1

Related Questions