asked
Why does my add column, insert not work
How come this throws an error, it says it can't find my column ALTER TABLE Class1 ADD OldId INT NULL INSERT INTO Class1(Name, Active, OldId ) SELECT M.Description, 1,
M.id FROM Makes M ALTER TABLE Class1 DROP COLUMN OldId but this works ALTER TABLE Class1 ADD OldId INT NULL GO INSERT INTO Class1(Name, Active, OldId ) SELECT M.Description, 1,
M.id FROM Makes M GO ALTER TABLE Class1 DROP COLUMN OldId
In the first instance it considers all the code as an implicit transaction and checks all objects involved. The engine doesn't "understand" that it creates the column first so when it checks for its existance in the insert portion it fails. The second example is a series of transactions and the object test is satisfied for the insert.
9 People are following this question.
