How to check correctly if a temporary table exists in SQL Server 2005?

Question

I have a query where I insert some values from a table:

SELECT ID, NAME INTO #tmpTable1 FROM TableOriginal 

First execution is fine, if I press F5(Run) in MSSMS (Microsoft Sql Server Management Studio), the error occured:

Msg 2714, Level 16, State 6, Line 4
There is already an object named '#tmpTable1' in the database.

Good. I decided to check before insert data from TableOriginal to #tmpTable1 using:

IF OBJECT_ID('tempdb.#tmpTable1') IS NOT NULL     DROP TABLE #tmpTable1 

Not working, the error shows again as above.

I saw in tempdb database the following temporary table name:

dbo.#tmpTable1__________________0000007 

Why? Every time when create a temporary table (using first query), the table name will be generated automatically in MSSMS ?

How to remove the existing temporary table to do a new table with new values ?

Answer 1

You're darn close - you need to use two dots in your check:

IF OBJECT_ID('tempdb..#tmpTable1') IS NOT NULL                       **                      |                   use two dots here! 

Basically, this is saying: check in the tempDB and I don't care what schema the table is in

As Joe rightfully said: this is not 100% correct: it doesn't check in every schema - it will only check in the default owner's schema - normally dbo. So this would work, too:

IF OBJECT_ID('tempdb.dbo.#tmpTable1') IS NOT NULL   

If you happen to create your objects in a schema other than the default owner's, then you'll need to explicitly specify the schema you're referring to. But the temp tables in tempDB are indeed creating in the dbo schema.