SQL Server 2012 – How to Refresh DDL Changes to Table

sql serversql-server-2012t-sql

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.

Best Answer

Add a batch separator between statements. Typically, the batch separator of choice would be GO:

--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
GO

CREATE TABLE tmp_xx_xx_testTable (u_id int, u_name varchar(10), u_add varchar(10))
GO

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
GO


EXECUTE sp_rename
                N'[dbo].[tmp_xx_xx_testTable]',
                N'TestTable';
GO

--SELECT * FROM testTable
--DROP TABLE testable

The reason it fails without separators is because, before the script can be executed, it must be compiled, and in order to be compiled, all the objects referenced by each statement must exist at the compile time. The parser, however, encounters statements that reference non-existent objects. The objects are supposed to be created by previous statements in your script, but, as the script is yet to execute, they are yet to be created as well. As the result, the missing objects fail the compilation of the script.

Adding separators is equivalent to your highlighting and executing the parts of the script between the separators, except that is done automatically by the management tool (SSMS or sqlcmd), which interprets the separators prior to sending the script (or, in this case, its portions) to the server.

Related Question