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 

Answer 1

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