Check if a temporary table exists and delete if it exists before creating a temporary table

Question

I am using the following code to check if the temporary table exists and drop the table if it exists before creating again  It works fine as long as I don t change the columns  If I add a column later  it will give an error saying  invalid column   Please let me know what I am doing wrong   IF OBJECT ID  tempdb   Results   IS NOT NULL     DROP TABLE  Results  CREATE TABLE  Results       Company                CHAR 3       StepId                TINYINT      FieldId                TINYINT     select company  stepid  fieldid from  Results  --Works fine to this point  IF OBJECT ID  tempdb   Results   IS NOT NULL     DROP TABLE  Results  CREATE TABLE  Results       Company                CHAR 3       StepId                TINYINT      FieldId                TINYINT      NewColumn            NVARCHAR 50     select company  stepid  fieldid  NewColumn from  Results  --Does not work

User · Answer

I recently saw a DBA do something similar to this   begin try     drop table  temp end try  begin catch      print  table does not exist  end catch   create table  temp a int  b int

User · Answer

I usually hit this error when I have already created the temp table  the code that checks the SQL statement for errors sees the  old  temp table in place and returns a miscount on the number of columns in later statements  as if the temp table was never dropped   After changing the number of columns in a temp table after already creating a version with less columns  drop the table and THEN run your query

User · Answer

This worked for me  social msdn microsoft com Forums en transactsql thread 02c6da90-954d-487d-a823-e24b891ec1b0 prof required  if exists       select    from tempdb dbo sysobjects o     where o xtype in   U        and o id   object id N tempdb   tempTable     DROP TABLE  tempTable

User · Answer

Note  This also works for    temp tables  i e  IF OBJECT ID  tempdb dbo   AuditLogTempTable1    U   IS NOT NULL DROP TABLE   AuditLogTempTable1  Note  This type of command only suitable post SQL Server 2016  Ask yourself    Do I have any customers that are still on SQL Server 2012   DROP TABLE IF EXISTS   AuditLogTempTable1

User · Answer

I cannot reproduce the error     Perhaps I m not understanding the problem   The following works fine for me in SQL Server 2005  with the extra  foo  column appearing in the second select result   IF OBJECT ID  tempdb   Results   IS NOT NULL DROP TABLE  Results GO CREATE TABLE  Results   Company CHAR 3   StepId TINYINT  FieldId TINYINT   GO select company  stepid  fieldid from  Results GO ALTER TABLE  Results ADD foo VARCHAR 50  NULL GO select company  stepid  fieldid  foo from  Results GO IF OBJECT ID  tempdb   Results   IS NOT NULL DROP TABLE  Results GO

User · Answer

Instead of dropping and re-creating the temp table you can truncate and reuse it   IF OBJECT ID  tempdb   Results   IS NOT NULL     Truncate TABLE  Results else     CREATE TABLE  Results               Company             CHAR 3           StepId              TINYINT          FieldId             TINYINT          If you are using Sql Server 2016 or Azure Sql Database then use the below syntax to drop the temp table and recreate it  More info here MSDN  Syntax     DROP TABLE   IF EXISTS     database name     schema name         schema name     table name       n     Query   DROP TABLE IF EXISTS tempdb dbo  Results CREATE TABLE  Results        Company             CHAR 3      StepId              TINYINT     FieldId             TINYINT

User · Answer

When you change a column in a temp table  you must drop the table before running the query again   Yes  it is annoying  Just what you have to do    I have always assumed this is because the  invalid column  check is done by parser before the query is run  so it is based on the columns in the table before it is dropped      and that is what pnbs also said

User · Answer

Just a little comment from my side since the OBJECT ID doesn t work for me  It always returns that        tempTable doesn t exist     even though it does exist  I just found it s stored with different name  postfixed by   underscores  like so     tempTable           This works well for me   IF EXISTS SELECT  name  FROM tempdb sys tables WHERE  name  like   tempTable    BEGIN    DROP TABLE  tempTable  END

User · Answer

My code uses a Source table that changes  and a Destination table that must match those changes   --  -- Sample SQL to update only rows in a  Destination  Table --  based on only rows that have changed in a  Source  table --   -- -- Drop and Create a Temp Table to use as the  Source  Table -- IF OBJECT ID  tempdb   tSource   IS NOT NULL drop table  tSource create table  tSource  Col1 int  Col2 int  Col3 int  Col4 int   -- -- Insert some values into the source -- Insert  tSource  Col1  Col2  Col3  Col4  Values 1 1 1 1  Insert  tSource  Col1  Col2  Col3  Col4  Values 2 1 1 2  Insert  tSource  Col1  Col2  Col3  Col4  Values 3 1 1 3  Insert  tSource  Col1  Col2  Col3  Col4  Values 4 1 1 4  Insert  tSource  Col1  Col2  Col3  Col4  Values 5 1 1 5  Insert  tSource  Col1  Col2  Col3  Col4  Values 6 1 1 6   -- -- Drop and Create a Temp Table to use as the  Destination  Table -- IF OBJECT ID  tempdb   tDest   IS NOT NULL drop Table  tDest create table  tDest  Col1 int  Col2 int  Col3 int  Col4 int   -- -- Add all Rows from the Source to the Destination -- Insert  tDest Select Col1  Col2  Col3  Col4 from  tSource   -- -- Look at both tables to see that they are the same -- select   from  tSource Select   from  tDest  -- -- Make some changes to the Source -- update  tSource     Set Col3 19     Where Col1 1 update  tSource     Set Col3 29     Where Col1 2 update  tSource     Set Col2 38     Where Col1 3 update  tSource     Set Col2 48     Where Col1 4  -- -- Look at the Differences -- Note  Only 4 rows are different  2 Rows have remained the same  -- Select Col1  Col2  Col3  Col4 from  tSource except Select Col1  Col2  Col3  Col4 from  tDest  -- -- Update only the rows that have changed -- Note  I am using Col1 like an ID column -- Update  tDest     Set Col2 S Col2          Col3 S Col3          Col4 S Col4 From        Select Col1  Col2  Col3  Col4             from  tSource             except             Select Col1  Col2  Col3  Col4             from  tDest           S Where  tDest Col1 S Col1   -- -- Look at the tables again to see that --  the destination table has changed to match --  the source table   select   from  tSource Select   from  tDest  -- -- Clean Up -- drop table  tSource drop table  tDest

User · Answer

The statement should be of the order   Alter statement for the table GO Select statement    Without  GO  in between  the whole thing will be considered as one single script and when the select statement looks for the column it won t be found   With  GO    it will consider the part of the script up to  GO  as one single batch and will execute before getting into the query after  GO

User · Answer

Yes   invalid column  this error raised from the line  select company  stepid  fieldid  NewColumn from  Results    There are two phases of runing t-sql    first  parsing  in this phase the sql server check the correction of you submited sql string  including column of table  and optimized your query for fastest retreival    second  running  retreiving the datas   If table  Results exists then parsing process will check the columns you specified are valid or not  else  table doesn t exist  parsing will be by passsed the checking columns as you specified

User · Answer

I think the problem is you need to add GO statement in between to separate the execution into batches  As the second drop script i e  IF OBJECT ID  tempdb   Results   IS NOT NULL DROP TABLE  Results did not drop the temp table being part of single batch  Can you please try the below script   IF OBJECT ID  tempdb   Results   IS NOT NULL     DROP TABLE  Results  CREATE TABLE  Results       Company                CHAR 3       StepId                TINYINT      FieldId                TINYINT     GO  select company  stepid  fieldid from  Results  IF OBJECT ID  tempdb   Results   IS NOT NULL DROP TABLE  Results  CREATE TABLE  Results       Company                CHAR 3       StepId                TINYINT      FieldId                TINYINT      NewColumn            NVARCHAR 50     GO  select company  stepid  fieldid  NewColumn from  Results

User · Answer

This could be accomplished with a single line of code    IF OBJECT ID  tempdb   tempTableName   IS NOT NULL DROP TABLE  tempTableName

User · Answer

This worked for me  IF OBJECT ID  tempdb dbo  tempTable   IS NOT NULL  DROP TABLE  tempTable    Here tempdb dbo dbo is nothing but your schema  is having more importance

User · Answer

pmac72 is using GO to break down the query into batches and using an ALTER   You appear to be running the same batch but running it twice after changing it      DROP    CREATE    edit    DROP    CREATE    Perhaps post your exact code so we can see what is going on

User · Answer

Now you can use the below syntax if you are using one of the new versions of SQL Server  2016     DROP TABLE IF EXISTS schema yourtable even temporary tables

[sql-server] Check if a temporary table exists and delete if it exists before creating a temporary table

Examples related to sql-server

Examples related to sql-server-2005

Examples related to temp-tables

Examples related to alter-table