check if temp table exist and delete if it exists before creating a temp table

Question

Hi,

I am using the following code to check if the temp 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 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

If I run the above code, it works fine. Now let's say I add a column fieldname and use that field in the select statement, it gives error.

Thanks, sridhar.

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

Answer 2

I can't reproduce the error either, but I wonder why the extra comma (after TINYINT) in the CREATE TABLE did not produce a syntax error

Answer 3

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.

Answer 4

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.