0
votes

A temporary table is created with a SELECT .. INTO statement

SELECT *
    INTO #MyTempTable
FROM ...

Under Databases, tempdb, Temporary Tables I see the temp table dbo.#MyTempTable____________________0000000016CA

Now I want to drop the table. I tried the following:

DROP TABLE IF EXISTS #MyTempTable

AND

IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL 
BEGIN 
    DROP TABLE #MyTempTable 
END

Both didn't delete the table

The Select returns NULL:

SELECT OBJECT_ID('tempdb..#MyTempTable') 
2
##MyTempTable <> #MyTempTable. - Larnu
DROP TABLE IF EXISTS #MyTempTable doesn't work either - smolo
@toms Are you committing or not? - Waqar Naeem
Yes, I executed and the result is "Command(s) completed successfully". But the table is still there - smolo

2 Answers

0
votes

You can use the trick with the OBJECT_ID.
But then make sure that the table that's checked for it's existance is the same as the one you drop.

IF OBJECT_ID('tempdb..#MyTempTable', 'U') IS NOT NULL 
BEGIN 
    DROP TABLE #MyTempTable;
END

Or simplified :

IF OBJECT_ID('tempdb..#MyTempTable', 'U') IS NOT NULL 
    DROP TABLE #MyTempTable;
0
votes

Pay attention to the name of checked table and dropped table.. #MyTempTable vs #lu_sensor_name_19

This is working for me:

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

if this does not work for you I think your problem is related to scope and/or visibility of your temp table.

temp tables exists only with the session where they were created, I guess you are dropping a temp table that doesn't exists amymore, or a temp table in another session.

first you have to check if the session in which you make the SELECT INTO is still active when you want to drop the table.

if answer is yes, then check if you are dropping from the same session or from another one.

in the second case you can solve the problem using ##MyTempTable instead of #MyTempTable

otherwise it means that something or someone else have already dropped it