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

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

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.

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'.

This worked for me:
[social.msdn.microsoft.com/Forums/en/transactsql/thread/02c6da90-954d-487d-a823-e24b891ec1b0?prof=required][1]

if exists (
select * from tempdb.dbo.sysobjects o
where o.xtype in ('U')

and o.id = object_id(N'tempdb..#tempTable')
)
DROP TABLE #tempTable;


[1]:

[To see links please register here]

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;

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

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

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)

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

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.