I am trying to create giant script which updates bunch of tables by adding/removing/modifying columns of existing database. And one issue that has stumped me is, whenever I add new column in table in one statement and try to refer it in another statement it fails with error about invalid column xxx.
Follow is pseudo code that I am using to reproduce this issue,
--CREATE TABLE testTable (u_id int, u_name varchar(10)) --INSERT INTO testTable VALUES (1,'11') --INSERT INTO testTable VALUES (2,'12') --INSERT INTO testTable VALUES (3,'13') --INSERT INTO testTable VALUES (4,'14') --INSERT INTO testTable VALUES (5,'15') BEGIN ALTER table testTable ADD u_add varchar(10) END BEGIN CREATE TABLE tmp_xx_xx_testTable (u_id int, u_name varchar(10), u_add varchar(10)) INSERT INTO dbo.tmp_xx_xx_testTable VALUES (6,'16','DDD' ) INSERT INTO tmp_xx_xx_testTable (u_id,u_name,u_add) SELECT u_id,u_name,u_add FROM TestTable drop TABLE TestTable EXECUTE sp_rename N'[dbo].[tmp_xx_xx_testTable]', N'TestTable'; END --SELECT * FROM testTable --DROP TABLE testable
First (and last) few lines are just to prepare test table that I am using.
When I run this code, I get an error about column
u_add not being valid. But when I run same statements individually, they run without any issues. So it looks like that somehow I am not understanding what else needs to be done here so SQL Server can actually "see" that newly added column.