October 03, 2010

Check if Temp Table Exists and Drop it in SQL Server




I use a lot of Temp Tables in my examples. A SqlServerCurry.com reader Rishab asked me if there was a way to check for an existing Temp Table and drop it using T-SQL. Temp Tables like Table Variables have a session level scope and not a statement-level scope, so the following query will give an error

CREATE TABLE #TmpTable(i int)
INSERT INTO #TmpTable
SELECT 345
GO
CREATE TABLE #TmpTable(i int)
INSERT INTO #TmpTable
SELECT 200

image

Note: Madhivanan wrote a nice post about Temp Table VS Table Variable in SQL Server

To avoid this error, here' a way to check if a Temp Table exists and drop it


-- Check if TmpTable exists and Drop it
IF OBJECT_ID('tempdb..#TmpTable') IS NOT NULL
DROP TABLE #TmpTable

-- Create TmpTable
CREATE TABLE #TmpTable(i int)
INSERT INTO #TmpTable
SELECT 200


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

0 Responses to "Check if Temp Table Exists and Drop it in SQL Server"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions