Insert data into temp table using select statement?

Question

I want to insert data manually into temporary table from select statement:

  Select into #temp from (select 1,2
    Union 
    select 2,4 
    Union 
    Select 8,12) as b

Mitch Wheat · Accepted Answer · 2019-08-30 08:09:42Z

4

You need to give columns a name (here I have named the columns a and b):

Select a, b into #temp 
from 
(
     select a = 1, b = 2 
     Union 
     select 2, 4 
     Union 
     Select 8, 12
) as t

select * from #temp

a   b
-----
1   2
2   4
8   12

Only the first SELECT clause of a UNION needs the explicit column names.

edited Aug 30, 2019 at 8:09

answered Aug 2, 2019 at 5:00

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mahadev dhyani Over a year ago

Do i need to pass the columns a and b in the inner query if i will pass select 1,2 instead of select a=1,b=2 ?

Mitch Wheat Over a year ago

I'm sure not what you mean. The above answers the question you asked.

jannowitz Over a year ago

@mahadevdhyani Mitch try to explain that a columnname is necessary. So he choose a and b. Otherwise you'll receive an Error "Server: Msg 8155 No column name was specified for column".

Sean Lange Over a year ago

You can remove the subquery portion of this by moving the into to the first query. It is syntactically a little odd but perfectly valid.

Collectives™ on Stack Overflow

Insert data into temp table using select statement?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related