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

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 #ResultsGOCREATE TABLE #Results ( Company CHAR(3), StepId TINYINT, FieldId TINYINT )GOselect company, stepid, fieldid from #ResultsGOALTER TABLE #Results ADD foo VARCHAR(50) NULLGOselect company, stepid, fieldid, foo from #ResultsGOIF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #ResultsGO

The statement should be of the order

1. Alter statement for the table
2. GO
3. 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'.

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 #Resultselse    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.#ResultsCREATE TABLE #Results  (   Company             CHAR(3),   StepId              TINYINT,   FieldId             TINYINT,  )