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?
Solution 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
)
Solution 2:
Check for the existence by retrieving its object_id:
if object_id('tempdb..##clients_keyword') is not null
drop table ##clients_keyword