Drop #Temp Table

Question

Post reply

Drop #Temp Table

Richard Sabbara

Ten Centuries

Points: 1382
More actions
July 20, 2006 at 6:48 am

#91234

Ok, I had this code before and now I can't find it, how do I get the code do drop a temp table at the start of my query.
Right now I am saying
Drop table #Temp
Select top 10 * from tableX into #TEMP
The first time that I run this I get an error statement, I need to put something into an SP and I have a lot of temp tables, I can't remember the code.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 1

if object_id( 'tempdb..#Temp' ) is not null drop table #Temp

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

If you Delcare a @TableVariable, you do not need to drop it to re-run the script...

I wasn't born stupid - I had to study.

l0n3i200n Ten Centuries Points: 1091 More actions · Answer 3

Declaring a table allow you to do normal stuff on it and have a type specific column - you then don't end up with foat when sql can't do correct formatting. Also the table is limited to the current scope of the query - no need drop as said by Farrel keough. Also I read a post somewhere that variable tables in much faster and beter that temp tables - think it was article "Temp Tables in SQL Server"

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 4

Table variables are not necessarily faster than Temp tables... they do not and cannot be made to use statistics...

Also, if you think that table variables might be faster because they live in memory, you really need to read the following URL... table variables and temp tables both start out in memory and they both spill into TempDB when they start to get large.

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

--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)

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

They also have scope issues. But, for quick a dirty, they are great! And, if you do not have large sets of data, they work dandy as well as Indexing is generally not a concern.

It is just less typing in Query Analyzer...

I wasn't born stupid - I had to study.

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

It is just less typing in Query Analyzer...

... unless you cheat and use SELECT/INTO... if you are confident that the code won't crash in the middle, you don't even need a "Drop" on either end of the proc (although I always include both just 'cause I'm a bit regimented [polite word for "anal" ) because Temp Tables are fairly scope sensitive as well. Can't even do a SELECT/INTO with a table variable.

--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)

Hans Lindgren SSChampion Points: 10496 More actions · Answer 7

...and as we all know it is a BIG no-no doing SELECT ... INTO ... FROM ... since it holds locks on a lot of system tables for the duration of the statement.