Is it possible to recreate a temporary table after dropping it?
Given:
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')
Problem:
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
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)
CREATE TABLE #foo(i INT);
ELSE
CREATE TABLE #foo(i VARCHAR(32));
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.
source to share
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
source to share