CODEWITHSUNDEEP
sqlsql-serversql-server-2017
Bob Horn
Bob Horn

Reputation: 34297

Why do I get an invalid column name error when using a temp table twice?

If I run each of these batches separately, it works. However, if they are combined into one script (like what is done when a DACPAC script runs, or putting them both into one tab in SSMS), I get an Invalid column name error on the second insert. Why is that? If I need these to run in one script, do I need to use a different name for the temp table for the second batch? Or am I missing something that would allow me to use the same name?

IF OBJECT_ID('tempdb..#source') IS NOT NULL DROP TABLE #source
SELECT FirstName, LastName INTO #source FROM Musician WHERE 1 = 0; -- set up temp table schema
INSERT INTO #source ( FirstName, LastName )
VALUES
('Geddy', 'Lee'),
('Alex', 'Lifeson') 

SELECT * FROM #source

GO

IF OBJECT_ID('tempdb..#source') IS NOT NULL DROP TABLE #source
SELECT [Name], Genre INTO #source FROM Band WHERE 1 = 0; -- set up temp table schema
INSERT INTO #source ( [Name], Genre )
VALUES
('Rush', 'Rock'),
('Ratt', 'Rock') 

SELECT * FROM #source

GO

Upvotes: 0

Views: 1284

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Each batch is parsed independently. So it works when you use GO because they are in different batches.

When you put everything in the same batch, SQL Server parses what it sees, and it is blind to logic like DROP commands hidden behind IF conditionals. Try the following and you'll find the same:

IF (1=0) DROP TABLE IF EXISTS #x; CREATE TABLE #x(i int);
IF (1=1) DROP TABLE IF EXISTS #x; CREATE TABLE #x(j date);

You and I both know that only one of those will ever execute, but the parser spots the redundant table name before it ever gets to execution (or evaluating any conditionals).

This works because, again, each batch is now parsed in isolation:

IF (1=0) DROP TABLE IF EXISTS #x; CREATE TABLE #x(i int);
GO
IF (1=1) DROP TABLE IF EXISTS #x; CREATE TABLE #x(j date);

This will in fact fail even though it passes parsing (highlight and select Parse instead of Execute), so the blindness goes both ways:

IF (1=0) DROP TABLE IF EXISTS #x; CREATE TABLE #x(i int);
GO
IF (1=1) CREATE TABLE #x(j date);

Upvotes: 2

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Using go after dropping the tables in both block will do the trick.

IF OBJECT_ID('tempdb..#source') IS NOT NULL DROP TABLE #source
go
SELECT FirstName, LastName INTO #source FROM Musician WHERE 1 = 0; -- set up temp table schema
INSERT INTO #source ( FirstName, LastName )
VALUES
('Geddy', 'Lee'),
('Alex', 'Lifeson') 

SELECT * FROM #source

GO

IF OBJECT_ID('tempdb..#source') IS NOT NULL DROP TABLE #source
go
SELECT [Name], Genre INTO #source FROM Band WHERE 1 = 0; -- set up temp table schema
INSERT INTO #source ( [Name], Genre )
VALUES
('Rush', 'Rock'),
('Ratt', 'Rock') 

SELECT * FROM #source

GO

Upvotes: 1

Related Questions