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
This question is related to
sql-server
sql-server-2005
temp-tables
alter-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 #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 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.
This worked for me: social.msdn.microsoft.com/Forums/en/transactsql/thread/02c6da90-954d-487d-a823-e24b891ec1b0?prof=required
if exists (
select * from tempdb.dbo.sysobjects o
where o.xtype in ('U')
and o.id = object_id(N'tempdb..#tempTable')
)
DROP TABLE #tempTable;
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
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.
This could be accomplished with a single line of code:
IF OBJECT_ID('tempdb..#tempTableName') IS NOT NULL DROP TABLE #tempTableName;
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
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 #...)
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 #Results
else
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.#Results
CREATE TABLE #Results
(
Company CHAR(3),
StepId TINYINT,
FieldId TINYINT,
)
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.
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
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;
The statement should be of the order
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'.
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.
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 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.
Source: Stackoverflow.com