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:

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.
[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]

9 comments
aaditi 09 May 2016 at 11:32 am

IF object_id(‘tempdb..#temptable’) IS NOT NULL
DROP TABLE #temptable

Hari 05 Feb 2014 at 4:08 pm
rory 22 Feb 2013 at 10:00 am

thanks,,,, you have no idea how much calm that bit of code is bringing to my life!!!

Derek Dieter 22 Feb 2013 at 4:13 pm

You mean no more changing the table name after each execution or copying and pasting the code into a new window? I can relate.

Matthew 28 Jan 2013 at 5:54 pm

A better version would use an N-string for Unicode support: i.e. N’tempdb..#Temp’

Derek Dieter 31 Jan 2013 at 6:04 pm

Ok, added. Thanks.

Derek

Jeffrey Smith 07 Dec 2011 at 10:24 am

derek,

thanks for the script. knew how to drop user tables, but wasn’t sure about the temp location.

jeff

Kyaw Kyaw Min 13 Sep 2011 at 12:48 am

how can be drop ##temp table without using database name .

Derek Dieter 16 Sep 2011 at 12:19 pm

Hi Kyaw,

In this case, you do need the database name of TempDB because that is always where temporary tables are stored.

Derek

Featured Articles

 Site Author

  • Thanks for visiting!
css.php