Drop a temporary table if it exists

Question

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 1

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   )