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

User · Answer

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

User · 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    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

User · Answer

Check for the existence by retrieving its object id   if object id  tempdb    clients keyword   is not null     drop table   clients keyword

[sql] Drop a temporary table if it exists

Examples related to sql

Examples related to sql-server