Pre-warning: This question may not make sense at first blush, hopefully though anyone can play along by trying this out on their local Sql Server instance.
With all that in mind, consider two scripts:
Create.sql:
Drop Table If Exists dbo.MyMysteryTable;
Create Table dbo.MyMysteryTable
(
A SmallInt Not Null
,B VarChar(32) Not Null
,C VarChar(128) Not Null
,D SmallDateTime Null
,E SmallInt Not Null
,F Char(1) Not Null
,G VarChar(16) Not Null
);
Insert dbo.MyMysteryTable(A, B, C, D, E, F, G)
Values (1, 'B1', 'C1', Null, 1, '1', 'G1')
,(2, 'B2', 'C2', Null, 2, '2', 'G2');
And the oddness that is
WTF.sql (a script where I'm basically rebuilding the table and renaming column C to Cx and then adding a new row to it.):
Drop Table If Exists #Temp;
Select A
,B
,C
,D
,E
,F
,G
Into #Temp
From dbo.MyMysteryTable;
Drop Table If Exists dbo.MyMysteryTable;
Create Table dbo.MyMysteryTable
(
A SmallInt Not Null
,B VarChar(32) Not Null
,Cx VarChar(128) Not Null
,D SmallDateTime Null
,E SmallInt Not Null
,F Char(1) Not Null
,G VarChar(16) Not Null
);
-- Insert #1
Insert dbo.MyMysteryTable(A, B, Cx, D, E, F, G)
Select *
From #Temp;
-- Insert #2
Insert dbo.MyMysteryTable(A, B, Cx, D, E, F, G)
Values (3, 'B3', 'C3', Null, 3, '3', 'G3');
I'm fully aware of Sql Server's very annoying predilection to sometimes fail to see updated metadata that quite often requires one to implement a Go to force it to "refresh" it's view of the underlying schema for the compiler.
However what threw me for an absolute loop was the parser accept Insert #1 succeed, but to reject Insert #2 with:
Msg 207, Level 16, State 1, Line 30
Invalid column name 'Cx'.
Both use the exact same column list, but only one is seen to be acceptable at compile time. I'm going to hazard a guess and say that Sql see's that the first Insert is using a temporary table, and so it may be looking at the metadata at a different level having to do a Table Scan and feed that into the Table Insert operator, as opposed to being able to use a direct insert for Insert #2.
Now the good news is that I have a very easy work around:
Workaround.sql:
Drop Table If Exists #Temp;
Select A
,B
,C
,D
,E
,F
,G
Into #Temp
From WTF.MyMysteryTable;
Drop Table If Exists WTF.MyMysteryTable;
Create Table WTF.MyMysteryTable
(
A SmallInt Not Null
,B VarChar(32) Not Null
,Cx VarChar(128) Not Null
,D SmallDateTime Null
,E SmallInt Not Null
,F Char(1) Not Null
,G VarChar(16) Not Null
);
-- Insert #2
Insert #Temp
Values (3, 'B3', 'C3', Null, 3, '3', 'G3');
-- Insert #1
Insert WTF.MyMysteryTable(A, B, Cx, D, E, F, G)
Select *
From #Temp;
Which works fine. I’m not sure why, because surely it still falls found if the rule about table changes and usages in the same batch, but I digress…
And yet...having worked, Sql Server gets further confused that if one then tries to rerun the original Create.Sql and WTF.sql in the same session as the one used to execute Workaround.sql, then it fails to parse Insert #1 (which is more to be expected).
Msg 207, Level 16, State 1, Line 25
Invalid column name 'Cx'.
If one reruns Create.sql and then picks a new session to run WTF.sql then it'll be back to failing on line 30.
This has me totally puzzled. Like I say, I have a work around so i'm not being held up, but I am totally fascinated as to why I'm observing this behavior.
Anyone out there with any ideas?
This has been tested against both Sql Server 2019 and Sql Server 2022 - using both SSMS and Azure Data Studio.
For what it's worth, I do have a Sql Fiddle but it's not very helpful because it apparently doesn't like #Temp tables.
NB: There's a bigger reason why I'm rebuilding the table - I just simplified my code steps here after this bit me on the rear end tonight.