CODEWITHSUNDEEP
sqlsql-servert-sqlsyntax
Heap of Pinto Beans
Heap of Pinto Beans

Reputation: 677

T-SQL syntax on creating temp table from union

This runs find by itself

(select * from #T1 
 union
 select * from  #T2)

This does not (added first line):

select * 
into #T3 
from
    (select * from #T1 
     union
     select * from  #T2)

It throws a syntax error.

What is the right syntax?

Upvotes: 0

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You don't need a subquery. Just add the into after the first select:

select *
into #T3
from #T1 
union
select *
from #T2;

Note that you should use union all unless you really do want to incur the overhead for removing duplicates.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

That is invalid syntax, you need alias while using result set from the derived table :

select * into #t3
from  (select col1, col2, . . . 
       from #t1
       union all
       select col1, col2, . . 
       from #t2
      ) t; --- alias missing

Upvotes: 2

Related Questions