drop existing temp table - SQL Server Forums

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

drop existing temp table

Author

Topic

bismarkcount
Starting Member

15 Posts

Posted - 2007-11-07 : 17:21:02

hello!! i am using a couple of temp tables for a select statement.
i need to drop the tables only if they exist before using them, because if i don't drop, then i will get this error:

There is already an object named '#Tmp01' in the database.

and if i try to drop the tables for the first time i run the query, then i will get an error saying that i cannot drop a table that doesn't exist.

i have tried using this sentence:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = '#Tmp01') DROP TABLE #Tmp01
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = '#Tmp02') DROP TABLE #Tmp02
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = '#Tmp03') DROP TABLE #Tmp03

but it only seems to work with normal tables, since temp tables are not found in sysobjects.

any suggestions??

thnx

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-07 : 17:28:23

try this...
IF OBJECT_ID('#Tmp01') IS NOT NULL DROP TABLE #Tmp01
IF OBJECT_ID('#Tmp02') IS NOT NULL DROP TABLE #Tmp02
IF OBJECT_ID('#Tmp03') IS NOT NULL DROP TABLE #Tmp03

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-07 : 17:28:28

temp tables reside in tempdb. you can do something like this:


IF Object_id('tempdb.dbo.#tmp') IS NOT NULL
	DROP TABLE #tmp  
CREATE TABLE #tmp (....)

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-07 : 17:29:00

sorry this...
IF OBJECT_ID('tempdb.dbo.#Tmp01') IS NOT NULL DROP TABLE #Tmp01
IF OBJECT_ID('tempdb.dbo.#Tmp02') IS NOT NULL DROP TABLE #Tmp02
IF OBJECT_ID('tempdb.dbo.#Tmp03') IS NOT NULL DROP TABLE #Tmp03

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-07 : 17:29:22