3

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.

  • 1
    "I am trying to create giant script" <-- This is often a bad idea. It seems like you're trying to create migration scripts. Consider tools that help you manage those changes, such as RoundhousE or Flyway. – jpmc26 Jan 19 '17 at 0:16
  • @jpmc26 I personally too think that creating big script is not a good idea. But given our client's environments and requirements we can't use any 3rd party tools (even if it is free and no installation is required). – JackLock Jan 20 '17 at 15:24
6

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.

  • That was it. Explanation makes it clearer. – JackLock Jan 20 '17 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.