Drop temp table if it exists

Temp #Tables are created in tempdb. Try this:

IF OBJECT_ID('tempdb..#lu_sensor_name_19') IS NOT NULL 
BEGIN 
    DROP TABLE #lu_sensor_name_19 
END

CREATE TABLE #lu_sensor_name_19...

SQL Server 2016 added the ability to do the drop in one line:

DROP TABLE IF EXISTS #lu_sensor_name_19 

CREATE TABLE #lu_sensor_name_19...

Use this.

IF OBJECT_ID('tempdb.dbo.##myTempTable', 'U') IS NOT NULL
BEGIN
    DROP TABLE ##myTempTable;
    --DROP TABLE ##tempdb.dbo.myTempTable;
    /* Above line commented out, because it generates warning:
    "Database name 'tempdb' ignored, referencing object in tempdb.",
    which is a pain in the neck if you are using a temp table to generate SQL code,
    and want to print the code to the screen.*/
END;
GO

CREATE TABLE ##myTempTable(
    FooBar nvarchar(128) not null,
);

And, in SQL Server 2016, you can write:

DROP TABLE IF EXISTS ##myTempTable

Tags:

Sql

Sql Server