Return to Question

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;

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 #2
Insert   #Temp
  Values (3, 'B3', 'C3', Null, 3, '3', 'G3');
-- Insert #1
Insert   dbo.MyMysteryTable(A, B, Cx, D, E, F, G)
Select   *
  From   #Temp;

However what threw me for an absolute loop was watching Insert #1 succeed, but for Insert #2 to fail with:

Which works fine.

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 on Insert #1 (which is more to be expected).

However what threw me for an absolute loop was the parser accept Insert #1 succeed, but to reject Insert #2 with:

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

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 on Insert #1!

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 on Insert #1 (which is more to be expected).