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

Sridhar_54429 0
Viewed 1804171

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
alter-table temp-tables sql-server-2005 sql-server
16 Answers

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
pmac72_214140

This could be accomplished with a single line of code: 

IF OBJECT_ID('tempdb..#tempTableName') IS NOT NULL DROP TABLE #tempTableName;
user_58508480

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 #...)
user_54980390

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_117693620

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_275350

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_2959140

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_53155810

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_43018990
Related