Fast way to create temp table from existing table definition

Question

Fast way to create temp table from existing table definition

Ted Manasa

Default port

Points: 1461
More actions
May 11, 2008 at 6:19 pm

#380440

A quick technique I'm sure many of you have already used, but thought I'd share anyway:
SELECT TOP 0 * INTO ##tempTable FROM existingTable
-- Update: Corrected thanks to Jeff Moden --
If you have any other (better) ways, please share.
Best,
---------------------------
|Ted Pin >>

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

Actually, the method you've shown won't work either... the INTO is in the wrong place. 😉 As a sidebar, you really shouldn't use global temp tables... to much of a chance of a collision with table names and schema differences.

Personally, I like the SELECT TOP 0 method better than the WHERE 1 = 0 method because it lets you know what it's doing at the very top of the query.

Also, in 90% of the cases, I'd use a SELECT list instead of *. Might even want to consider always using a predefined table... makes it easier to troubleshoot and cuts down on recompiles if you create the table(s) at the very beginning of the sproc.

{Edit}... also, all this worry about SELECT/INTO causes blocks and therefor requires WHERE 1=0 or TOP 0 is an (as Matt calls it below), an urban legend. Yes, it used to be true... but hasn't been since a fix was made in 6.5... see my post further below, for details on this...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ted Manasa Default port Points: 1461 More actions · Answer 2

Jeff Moden (5/11/2008)
Actually, the method you've shown won't work either... the INTO is in the wrong place. 😉 As a sidebar, you really shouldn't use global temp tables... to much of a chance of a collision with table names and schema differences.
Personally, I like the SELECT TOP 0 method better than the WHERE 1 = 0 method because it lets you know what it's doing at the very top of the query.
Also, in 90% of the cases, I'd use a SELECT list instead of *. Might even want to consider always using a predefined table... makes it easier to troubleshoot and cuts down on recompiles if you create the table(s) at the very beginning of the sproc.

Thanks, Jeff, I fixed it 😀

You're right about local vs. global temp tables. We had to use this method because of a restriction of SSIS packages (which I can't remember at the moment...).

And I also agree that one should use a SELECT list instead of *, but we decided to go this route to provide flexibility when we change the table - the SSIS package that creates the temp table doesn't have to be modified and sent through change control.

I guess I should have noted that the above method was designed around the contraints of SSIS!

---------------------------
|Ted Pin >>

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

Theanks for the feedback, Ted... I'll add those to the list of reasons why I don't use DTS or SSIS 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

KingGorilla Old Hand Points: 377 More actions · Answer 4

Guys

I'd just like to confirm this with you... as it's bang on with a conversation i've had today.

I advised someone not to use SELECT... INTO #TMPTABLE due to poss issues around locking tempdb while it creates and populates the tmptable. I mentioned about the where 1=0 route (nice to know about the select top 0 btw) but I was told that SELECT... INTO #TMPTABLE in 2005 is not worth worrying about... personally i could not see it and i'm going to stick with creating the #tmptable and then inserting into it but it'd be good to get some more views on this... 😀

KG

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Actually - the other person was me, and the comment centered around the "locking TempDB" urban legend that seems to pop up a lot. That got a lot of publicity due to some nasty bug whic was fixed in 7.0, so the locking issue is by far and large gone.

That being said - a SELECT...INTO fairly far down in a SP might cause some amount of recompiling (although in 2005 you can force it to only recompile affected statements instead of the entire SP), so the concern about recompiles is worth taking into account.

Select...INTO has its place, and used correctly will actually outperform CREATE TABLE...INSERT INTO..... It comes down to knowing when and how to use it.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

Matt Miller (6/19/2008)
Actually - the other person was me, and the comment centered around the "locking TempDB" urban legend that seems to pop up a lot. That got a lot of publicity due to some nasty bug whic was fixed in 7.0, so the locking issue is by far and large gone.

The Microsoft bug fix article about this didn't concern just TempDB... it concerned whatever database the table was being created in.

http://support.microsoft.com/kb/153441/EN-US/

Basically, the 6.5 "bug" was that the table create and population of a SELECT INTO were treated as if they were a single explicit transaction. That is no longer the case and you can, in fact, create other tables in other connections right in the middle of extremely long winded SELECT INTO's.

Heh.. and like everything else I open my big mouth about, yes, I've tested it...

Run this in one window...

WAITFOR TIME '12:31:00'

--===== Create and populate a 1,000,000 row test table.

-- Column "RowNum" has a range of 1 to 100,000 unique numbers

-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times

-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'

-- for all rows.

-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

-- Jeff Moden

SELECT TOP 5000000

SomeID = IDENTITY(INT,1,1),

SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),

SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),

SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),

SomeHex12 = RIGHT(NEWID(),12)

INTO dbo.JBMTest

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

--===== A table is not properly formed unless a Primary Key has been assigned

-- Takes about 1 second to execute.

ALTER TABLE dbo.JBMTest

ADD PRIMARY KEY CLUSTERED (SomeID)

... and run this in another window (both should be visible at the same time so you can confirm what's going on)... Also notice the WAITFOR TIME should be about 10 seconds after the WAITFOR TIME in the code above.

WAITFOR TIME '12:31:10'

CREATE TABLE Dodah (N INT)

The first chunck of code takes about 5 minutes to run... even if you start the code and stop it, you'll notice that dbo.JbmTest has already been created which demonstrates that it's no longer part of the transaction.

Also notice, that the second code runs immediately even though the first code is still running. If there were any blocks on the system tables, the second code would not run until the first code completed.

And, yes... I've tested it by making the tables in all system databases... not just TempDB or user created databases.

Like Matt said... all of this fear about SELECT INTO is based on an Urban Legend that actually had some truth to it way back in 6.5. The 1=0 special handling of SELECT/INTO just isn't required to be safe anymore.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

Heh... I think I just gave myself an idea for an article 😉 This same fear/question get's asked a lot...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Jeff Moden (6/19/2008)
Matt Miller (6/19/2008)
Actually - the other person was me, and the comment centered around the "locking TempDB" urban legend that seems to pop up a lot. That got a lot of publicity due to some nasty bug whic was fixed in 7.0, so the locking issue is by far and large gone.
The Microsoft bug fix article about this didn't concern just TempDB... it concerned whatever database the table was being created in.
http://support.microsoft.com/kb/153441/EN-US/

Yup...I was merely "backreferencing" the previous conversation KingG and I were having, but yes it affected any DB.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

Heh... yeah... I knew that... I was just clarifyin' 😀 Wanted to amplify what you were saying because it's such a myth now.

It's amazing to me that this type of stuff is carried forward year after year after year... And, no one actually tests it! They just do the SQL Clone thing and carry the myth forward.

I first heard of the problem way back in circa '95 when I first started using SQL Server. I forget which year it was actually fixed, but it had to be at least 12 years ago... think about it... something that was fixed 12 years ago, yet the fear has been perpetuated by the uniformed and the intellectually non-curious... wow... 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ted Manasa Default port Points: 1461 More actions · Answer 10

I think these kinds of myths are the result of the feeling of importance harbingers of doom get when they promulgate morsels of misinformation that are hard to refute--and when they are refuted they can say, "Oh, yeah, sorry, that was in a prior version. I just didn't know if it was fixed in the new one..."

:w00t::hehe:

---------------------------
|Ted Pin >>

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 11

Heh... well said, Ted. That's why one of my favorite lines is "Really? Do you have an MS URL or some test code that proves that?" 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

True....but some of it really is stuff that just isn't documented. Those things make it awfully hard to throw out things outright, and force you to test them.

Like this interesting behavior brought up by Jeff Williams this morning:

http://www.sqlservercentral.com/Forums/FindPost519986.aspx

Try finding that documented somewhere... and yet - it seems to be true for 2000 and NOT for 2005. The documentation is hard to track these days, especially when subtle changes are introduced between versions (like the extra date formats that popped up in 2005).

By the way - speaking of "urban legends", here's one that just got blown away. I could see why this might not need to "get out" into the wild, but Paul Randal decided to air this anyway. Hint - read the end of the 6/11 post....

http://www.sqlskills.com/blogs/paul/default,month,2008-06.aspx

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 13

Matt Miller (6/19/2008)
True....but some of it really is stuff that just isn't documented. Those things make it awfully hard to throw out things outright, and force you to test them.

Yep... I agree... Microsoft doesn't document everything... that's when testing and sample code play such an important role. Ryan did a pretty good job of documenting what he found... that's pretty cool since he reportedly wrote a lot of the stuff.

That's also the reason why articles like the "Running Total" article were so bloody long... there were a lot of "myths" to contend with and each one had to be proven by code, one way or the other.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

KingGorilla Old Hand Points: 377 More actions · Answer 14

Thanks for clarifying that, i was going to say i'd better find out for myself and test it but as ever it looks like it's been extensively tested already.

I now consider myself more informed and will not continue to perpetuate this myth 😀