DROP Table #TempTable

Transact-SQL
1

When I use the following in a script, it does not DROP the table. I would have to highlight this line and execute it by itself and when running the entire script, it works. Is it because I am missing a semicolon? I have tried to replicate the error but sometimes the script runs entirely and other times, it errors out and I would have to manually drop the TEMP table.

IF OBJECT_ID('tempDb..#TempTable') IS NOT NULL DROP TABLE #TempTable

2

It just happened again and I put in a "GO" and I believe it worked.

IF OBJECT_ID('tempDb..#TempTable') IS NOT NULL DROP TABLE #TempTable
GO
3

It should always work if you execute the script. One minor niggle I have with this is it confuses the "Display Estimated Execution Plan" code and errors with table already exists, so you need to drop all temp tables first if you're going to use that.

With 2017 you can use the new DIE syntax, DROP TABLE IF EXISTS #TempTable, which is cleaner but doesn't avoid the execution plan issue either.

1 Like
4

Yes Andy, that is what happened, I would have to drop all the temp tables first. But it did work when I put GO after each one.

We are still on version 2014 so I am not sure if "the new DIE syntax, DROP TABLE IF EXISTS #TempTable" will work. I will try it tomorrow when I am work.

Thank you Andy.

5

DROP TABLE IF EXISTS was introduced in SQL Server 2016

1 Like