How To Drop Temp Table If Exists In SQL Server

Note that temporary tables are automatically deleted when the session ends; however, you can explicitly delete the temp table using the approaches below.

Approach-1 Using the DROP TABLE IF EXISTS

Syntax

DROP TABLE IF EXISTS #temptablename;

For example, Let us try to delete an existing temp table named #Product. Before that, we can check with the select query to see if it exists, as shown in the screenshot below.

Select * from #Product;

Now, let us try deleting this table. We can execute the below query for this purpose.

DROP TABLE IF EXISTS #Product;

The query was executed successfully, and the temp table was deleted without issues, as shown in the screenshot below.

Let us cross-check by running the same Select statement. You can see we got an error this time: “Invalid object name ‘#Product.'” This error is because the table doesn’t exist now. You can check out the screenshot below for your reference.

Select * from #Product;

Check out How To Create A Temp Table In SQL Server

Approach-2 Using IF EXISTS

You can use the IF EXISTS statement for this purpose.

Syntax

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
    DROP TABLE #TempTable

Example

Let’s drop the #Vehicle table using the below query.

IF OBJECT_ID('tempdb..#Vehicle') IS NOT NULL
    DROP TABLE #Vehicle

After executing the above query, I got the expected output, as shown in the screenshot below.

Check out How To Insert Into Temp Table In SQL Server

Approach-3 Using the TRY-CATCH block

You can also use the try-catch block along with the Drop table statement.

Syntax

BEGIN TRY
    DROP TABLE #Temptable
END TRY
BEGIN CATCH
    -- do something
END CATCH

Example

Let us drop the table name #VehicleNew.

BEGIN TRY
    DROP TABLE #VehicleNew
END TRY
BEGIN CATCH
    PRINT 'Temp table doesnot exists';
END CATCH

After executing the above query, we got the expected output, as shown in the screenshot below.

Conclusion

Dropping temporary tables in SQL Server if they exist using different approaches, as mentioned in this article.

You may also like following the articles below.

• How To Insert Values Into Table With Identity Column In SQL Server
• How to truncate table in SQL Server
• How to find column name in table SQL Server