Drop Temp Table If Exists

If you’re here then you’ve probably run into the situation where you’ve automatically created a temp table in your script, and every time you execute the script you have to drop the temp table manually. Yes, this can be a pain. I’ve done this myself many times.

So here’s the easy solution. We need to check if the temp table exists within the TempDB database and if it does, we need to drop it.

[cc lang=”sql”]
IF OBJECT_ID(N’tempdb..#Temp’) IS NOT NULL
BEGIN
DROP TABLE #Temp
END
[/cc]

To replicate this, let’s run the following command in the same window multiple times:
[cc lang=”sql”]
— (Replace the product table below with your table)
SELECT *
INTO #Temp
FROM SalesLT.Product
[/cc]
We get the error message:

Now we simple add our snippet of code, and we are able to execute without having to manually drop anymore.
[cc lang=”sql”]
IF OBJECT_ID(‘tempdb..#Temp’) IS NOT NULL
BEGIN
DROP TABLE #Temp
END

— (Replace the product table below with your table)
SELECT *
INTO #Temp
FROM SalesLT.Product
[/cc]