CODEWITHSUNDEEP
sql-server
Soner from The Ottoman Empire
Soner from The Ottoman Empire

Reputation: 20901

Unioned query result to temp table

I have a subquery which I want to insert into a temp table. But, I get syntax error such as SQL Error [102] [S0001]: Incorrect syntax near ')'..

select *
into #TempTable
from (
        SELECT x,y,z
        FROM 
         schemaA.tableC
        where (x  = '1234')
        UNION 
        SELECT x,y,z
        FROM 
         schemaB.tableC
        where (x  = '1234')
)

select *
from #TempTable

Upvotes: 1

Views: 449

Answers (2)

Grant Fritchey
Grant Fritchey

Reputation: 2775

Add an alias to the derived table and you'll be good to go:

select a.*
into #TempTable
from (
        SELECT x,y,z
        FROM 
         schemaA.tableC
        where (x  = '1234')
        UNION 
        SELECT x,y,z
        FROM 
         schemaB.tableC
        where (x  = '1234')
) AS a;

select *
from #TempTable;

Upvotes: 1

Peter Smith
Peter Smith

Reputation: 5550

You need to give an alias to the inner query:

select *
into #TempTable
from (
        SELECT x,y,z
        FROM 
         schemaA.tableC
        where (x  = '1234')
        UNION 
        SELECT x,y,z
        FROM 
         schemaB.tableC
        where (x  = '1234')
) AS P

select *
from #TempTable

Simply, the structure is:

SELECT
    *
FROM
   (SELECT * FROM MyTable) AS P

Upvotes: 2

Related Questions