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


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

No comments: