Using "union" with "select into"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using "union" with "select into"

Author

Topic

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-07-05 : 10:40:53

Hi is it not possible to use union with select into?

Select blah From blah
union all
Select blah2 from blah2
into #temptable

Cheers

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-05 : 10:44:35

Select * into #temptable
from
(
Select blah From blah
union all
Select blah2, from blah2
) T

Madhivanan

Failing to plan is Planning to fail

sshelper
Posting Yak Master

216 Posts

Posted - 2007-07-05 : 10:47:28

The INTO clause must be after the first SELECT statement:

Select blah From blah
INTO #temptable
union all
Select blah2, from blah2

SQL Server Helper
http://www.sql-server-helper.com

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-07-05 : 10:54:17

quote:
The INTO clause must be after the first SELECT statement:

Select blah From blah
INTO #temptable
union all
Select blah2, from blah2

I tried that, but it didn't appear to work for me. Maybe i typoed.

Madhivanan's works a treat though. What's the T at the end of the statement?

Select * into #temptable
from
(
Select blah From blah
union all
Select blah2, from blah2
) T

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-05 : 11:04:29

It's called an alias (to give name to the derived table)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sshelper
Posting Yak Master

216 Posts

Posted - 2007-07-05 : 11:06:49

quote:
Originally posted by drewsalem

quote:
The INTO clause must be after the first SELECT statement:

Select blah From blah
INTO #temptable
union all
Select blah2, from blah2

I tried that, but it didn't appear to work for me. Maybe i typoed.

The INTO must be after the first SELECT but before the FROM clause of that first SELECT:

Select blah From blah
INTO #temptable
FROM YourTable1
union all
Select blah2, from blah2
FROM YourTable2

SQL Server Helper
http://www.sql-server-helper.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources