Drop a temporary table if it exists

SqlSql Server

Sql Problem Overview


I have two lines of code in SQL that create two tables on the fly, i need to do something like

IF TABLE EXISTS 
    DROP IT AND CREATE IT AGAIN
ELSE
    CREATE IT

my lines are the following ones

CREATE TABLE ##CLIENTS_KEYWORD(client_id int)     
CREATE TABLE ##TEMP_CLIENTS_KEYWORD(client_id int)   

how can I apply that concept for these two tables in my procedure?

Sql Solutions


Solution 1 - Sql

From SQL Server 2016 you can just use

 DROP TABLE IF EXISTS ##CLIENTS_KEYWORD

On previous versions you can use

IF OBJECT_ID('tempdb..##CLIENTS_KEYWORD', 'U') IS NOT NULL
/*Then it exists*/
DROP TABLE ##CLIENTS_KEYWORD
CREATE TABLE ##CLIENTS_KEYWORD
(
   client_id INT
)

You could also consider truncating the table instead rather than dropping and recreating.

IF OBJECT_ID('tempdb..##CLIENTS_KEYWORD', 'U') IS NOT NULL
  TRUNCATE TABLE ##CLIENTS_KEYWORD
ELSE
  CREATE TABLE ##CLIENTS_KEYWORD
  (
     client_id INT
  ) 

Solution 2 - Sql

Check for the existence by retrieving its object_id:

if object_id('tempdb..##clients_keyword') is not null
    drop table ##clients_keyword

Solution 3 - Sql

What you asked for is:

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

       CREATE TABLE ##CLIENTS_KEYWORD(client_id int)

    END
ELSE
   CREATE TABLE ##CLIENTS_KEYWORD(client_id int) 

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

       CREATE TABLE ##TEMP_CLIENTS_KEYWORD(client_id int)

    END
ELSE
   CREATE TABLE ##TEMP_CLIENTS_KEYWORD(client_id int) 

Since you're always going to create the table, regardless of whether the table is deleted or not; a slightly optimised solution is:

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

CREATE TABLE ##CLIENTS_KEYWORD(client_id int) 

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

CREATE TABLE ##TEMP_CLIENTS_KEYWORD(client_id int) 

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionuser710502View Question on Stackoverflow
Solution 1 - SqlMartin SmithView Answer on Stackoverflow
Solution 2 - SqlDerek KrommView Answer on Stackoverflow
Solution 3 - SqlWonderWorkerView Answer on Stackoverflow