Observer Observer - 5 months ago 27
SQL Question

recycle temp table name

I have the following code.

If OBJECT_ID('tempdb.dbo.#tempTable', 'U') is not null
drop table #tempTable;

select 'bacon' into #tempTable
select * from #tempTable

If OBJECT_ID('tempdb.dbo.#tempTable', 'U') is not null
drop table #tempTable;

select 'sandwich' into #tempTable
select * from #tempTable



  1. drop the temp table if it exists

  2. create / populate it with a select into statement

  3. drop the temp table if it exists

  4. create / populate it with a select into statement



This is how I read the code I have written, but I think SSMS is reading it differently because I get error message:


Msg 2714, Level 16, State 1, Line 11 There is already an object named
'#tempTable' in the database.


I feel that maybe the If Object_ID parts might be getting hoisted to the top of code, or something with order of execution. The table doesn't get dropped at the 2nd drop table statement. I can of course use different temp tables as a work around, but I want to understand why this example doesn't work.

Answer

My assumption is that it thinks the temp table is still there as part of the batch.

If OBJECT_ID('tempdb.dbo.#tempTable', 'U') is not null
drop table #tempTable;

select 'bacon' into #tempTable
select * from #tempTable

If OBJECT_ID('tempdb.dbo.#tempTable', 'U') is not null
drop table #tempTable;

GO --At least this should do the trick

select 'sandwich' into #tempTable
select * from #tempTable

Although Microsoft suggests "DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur."

Also note, if you are using this in a stored procedure with variables, after GO the variables will no longer be there.

Comments