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
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
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
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