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.
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
DROP TABLE #Temp
END
To replicate this, let’s run the following command in the same window multiple times:
-- (Replace the product table below with your table) SELECT * INTO #Temp FROM SalesLT.Product
We get the error message:
There is already an object named '#Temp' in the database.
Now we simple add our snippet of code, and we are able to execute without having to manually drop anymore.
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
Popular search terms:
derek,
thanks for the script. knew how to drop user tables, but wasn’t sure about the temp location.
jeff
how can be drop ##temp table without using database name .
Hi Kyaw,
In this case, you do need the database name of TempDB because that is always where temporary tables are stored.
Derek