user710502 user710502 - 5 months ago 24
SQL Question

Drop a temporary table if it exists

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?

Answer

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') 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') IS NOT NULL
  TRUNCATE TABLE ##CLIENTS_KEYWORD
ELSE
  CREATE TABLE ##CLIENTS_KEYWORD
    (
       client_id INT
    ) 
Comments