Thursday, February 11, 2010

Checking if a table exists before dropping it

I often see folks create "temporary" tables that are not true #temp tables - with the intent of refreshing the entire table's contents.  This could be for data warehousing purposes or to simply have a table of aggregated data in your OLTP system for reporting purposes.  To ensure the table is not there before you execute your SELECT INTO statement, use the following syntax:

IF OBJECT_ID('YourTempTable') IS NOT NULL
     DROP TABLE YourTempTable

-- then populate the table with your data
SELECT * INTO YourTempTable FROM...

Pretty straight forward.