Is it possible to recreate a temporary table after dropping it?


inside a stored procedure:

select bleh
  into #tblTemp
  from FunctionThatReturnsTable('some','params')

-- do some stuff

drop table #tblTemp

-- Error on this command:
-- 'There is already an object named '#tblTemp' in the database.'
select bleh
  into #tblTemp
  from FunctionThatReturnsTable('some','other params')



I am unable to recreate this temporary table. My job is to use # tmpTable1, # tmpTable2, # tempTable3, etc. Is there a way to get around this? It would be nice to just use one temporary table each time.

If not, what is the reason for this?


source to share

3 answers

As reflected in my comment, I'm going to assume the answer is that you use a different table name #temp for each object you create. It's kind of like telling the doctor, "It hurts when I do this." His answer will probably be "stop doing this!"

The reason this is a problem is because the SQL Server parser is trying to parse the entire batch in one shot. It is clear that you are trying to create the same #temp table multiple times, but ignoring the command in DROP

between (I cannot say for sure why this is, since I have no access to the source code). For the same reason, you cannot do this:

IF (1=1)


The parser sees two identical names, but cannot follow the logic IF/ELSE


In addition to eliminating parser-induced problems with multiple like-named #temp tables, another advantage of using unique names is that they can be reused if you don't explicitly delete them. This will ease the load on tempdb in terms of metadata / blocking.



I ran into this problem when deleting + inserting a column. Probably the problem is with the parser, that it "recognizes" the table on first creation and does not see that it has been deleted.

I would suggest using exec sp_executesql 'create table'



This is a feature by design and is being clarified by Microsoft vs. Microsoft Connect Bug ID 666430

Please see the case study on the same temporary-table-could-not-be-re-created



All Articles