I am using the following script to add a column to my table. When I hit F5 in the query window it gives an error on the 2nd update query that fkRefTabNo doesn't exist.
ALTER TABLE EPFieldSQLScripts
ADD fkRefTabNo int DEFAULT 1
update EPFieldSQLScripts
set fkRefTabNo = 1
where fkRefTabNo is null
ALTER TABLE EPFieldSQLScripts
ALTER COLUMN fkRefTabNo INTEGER NOT NULL
But when I run these queries one by one, it doesn't give an error. Can anyone tell me what is wrong with this script?
Put GO between statements. This way the result of each statement will be committed before running the next one (each statement will be a separate transaction).
ALTER TABLE EPFieldSQLScripts
ADD fkRefTabNo int DEFAULT 1
GO
update EPFieldSQLScripts
set fkRefTabNo = 1
where fkRefTabNo is null
GO
ALTER TABLE EPFieldSQLScripts
ALTER COLUMN fkRefTabNo INTEGER NOT NULL
As others have answered, adding GO is the solution. It's not the reason though
SQL Server is trying to run your entire script as a single transaction, and then only committing the data to the database when the script completes. So at the point at which
update EPFieldSQLScripts
set fkRefTabNo = 1
where fkRefTabNo is null
is run (and queued ready for commit), the field fkRefTabNo has not actually been added to the database table (which only happens on commit). Adding the GO statements - or, running each statement individually - commits the transactions to the database before continuing with the next statement, hence why you're seeing a difference in behaviour.
Try adding GO in between each of these statements the outcome should be the same as executing each one seperately.
Put ; at the end of each query statement
Like
ALTER TABLE EPFieldSQLScripts ADD fkRefTabNo int DEFAULT 1;
update EPFieldSQLScripts set fkRefTabNo = 1 where fkRefTabNo is null;
ALTER TABLE EPFieldSQLScripts;
ALTER COLUMN fkRefTabNo INTEGER NOT NULL;
Now it must work together.
; is no requirement in TSQL only for some minor exceptions for example when using WITH