select * into #tmptable from select

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

General SQL Server Forums

New to SQL Server Programming

select * into #tmptable from select

Author

Topic

tpiazza55
Posting Yak Master

162 Posts

Posted - 2010-11-01 : 12:31:15

how do i do this:

select * into #tmptable from (select * from test)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-01 : 12:35:31

Maybe with an alias name for your derived table?
select * into #tmptable from (select * from test) as dt

No, you're never too old to Yak'n'Roll if you're too young to die.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-01 : 12:40:57

select * into #tmptable from test

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-01 : 12:46:16

LOL.
I am assuming he/she wants more than the example shows us...

No, you're never too old to Yak'n'Roll if you're too young to die.

tpiazza55
Posting Yak Master

162 Posts

Posted - 2010-11-01 : 13:03:06

that might get me going in the right direction.

if i have a join that results in 2 columns having the same name how do i get around that in a derived table?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-01 : 13:08:46

For example:
(select t1.col1, t2.ColA from table1 t1 join table2 t2 on t1.id=t2.id) as dt

Outside the derived table the two columns are:
dt.col1 and dt.ColA

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-01 : 13:08:53

quote:
Originally posted by tpiazza55

that might get me going in the right direction.

if i have a join that results in 2 columns having the same name how do i get around that in a derived table?

rename one of columns into some other name using AS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-01 : 13:10:01

Ah, I misunderstood.

No, you're never too old to Yak'n'Roll if you're too young to die.

tpiazza55
Posting Yak Master

162 Posts

Posted - 2010-11-01 : 13:16:49

thanks alot -- got it going now

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-01 : 13:23:39

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-11-01 : 13:25:05

Is there a difference between

select * into #tmptable from test
and
select * into #tmptable from (select * from test) as dt

if [test] has a column which is IDENTITY? (the first one will create the column in #tmptable with IDENTITY attribute, but will the second one too?)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-01 : 13:45:31

quote:
Originally posted by Kristen

Is there a difference between

select * into #tmptable from test
and
select * into #tmptable from (select * from test) as dt

if [test] has a column which is IDENTITY? (the first one will create the column in #tmptable with IDENTITY attribute, but will the second one too?)

Yes.
I have made a short test...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources