If a temporary table already exists, remove it.

Problem

I have two lines of SQL code that generate two tables on the fly, and I need to do something along those lines.

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

The following are my lines:

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

How can I use that concept in my method for these two tables?

Asked by user710502

Solution #1

You can just use SQL Server 2016 from now on.

 DROP TABLE IF EXISTS ##CLIENTS_KEYWORD

You can use it in prior versions.

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

Instead of dropping and recreating, you may consider truncating the table.

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

Answered by Martin Smith

Solution #2

Retrieve the object id to check for its existence:

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

Answered by Derek Kromm

Solution #3

What you’ve requested 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) 

Because you’ll always build the table, regardless of whether it’s erased or not, here’s a little improved solution:

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) 

Answered by WonderWorker

Post is based on https://stackoverflow.com/questions/7259285/drop-a-temporary-table-if-it-exists