Using DROP TABLE IF EXISTS When Working with Temporary Tables in SQL Server

by

In SQL Server, when working with temporary tables, it’s common to ensure a temp table with the same name doesn’t already exist before creating it. This prevents any unwanted errors. SQL Server doesn’t support CREATE TABLE IF NOT EXISTS like some other DBMSs do, so you must explicitly check and drop the table beforehand.

Using DROP TABLE IF EXISTS (SQL Server 2016 and Later)

Starting with SQL Server 2016, you can use DROP TABLE IF EXISTS to simplify the process. This allows you to avoid errors that would occur if the script attempts to create a temp table that already exists.

Example:

-- Drop the temp table if it already exists
DROP TABLE IF EXISTS #MyTempTable;

-- Create the temp table
CREATE TABLE #MyTempTable (
    ID INT,
    Name NVARCHAR(50)
);

Using OBJECT_ID() to Drop Tables (Pre-SQL Server 2016)

For older versions of SQL Server (prior to 2016), you can use the OBJECT_ID() function to conditionally drop temp tables. This function returns the object identification number for a schema-scoped object, or NULL if the object doesn’t exist.

Example:

-- Drop the temp table if it already exists
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
    DROP TABLE #MyTempTable;

-- Create the temp table
CREATE TABLE #MyTempTable (
    ID INT,
    Name NVARCHAR(50)
);

SQL Server Does Not Support CREATE TABLE IF NOT EXISTS

The above examples dropped the table if it exists, before creating a fresh table from scratch. This therefore removes the original temp table along with any data that was stored in it. This may or may not be what you want. Sometimes you might want to retain any existing temp table, so that you only create the new one if it doesn’t exist.

Some relational databases (like MySQL or PostgreSQL) support the CREATE TABLE IF NOT EXISTS syntax for this very purpose. But SQL Server doesn’t.

Fortunately, we can use a conditional structure as a workaround. It’s similar to the condition we used to drop the temp table in the above example (which deleted the table if it was not NULL), but this time we’re going to create the table if it is NULL.

Example:

IF OBJECT_ID('tempdb..#MyTempTable') IS NULL
BEGIN
    CREATE TABLE #MyTempTable (
        ID INT,
        Name NVARCHAR(50)
    );
END

This checks the tempdb system database for the existence of the local temporary table and only creates it if it doesn’t exist. As mentioned, OBJECT_ID() returns the object identification number for a schema-scoped object, or NULL if the object doesn’t exist. Therefore, if it returns NULL then the table doesn’t exist, and we can go ahead and create it.