sql server - Run a migration script where a column is created and then updated -

i have script want add surveyid , remove contractid sor table. script not run because surveyid created in script , considered invalid column not exist before created. how fix this? see

-- start of script print 'start of script'  if not exists ( select  1                    information_schema.columns                   table_schema = 'dbo'                         , table_name = 'sor'                         , column_name = 'contractid' )     begin         print 'the colmun contractid not exist in sor table'          goto endscript     end  begin transaction go if @@error <> 0 begin     goto errorscript end  print 'begin transaction'  -- add new surveyid field both questions , sor if exists ( select  1                    information_schema.columns                   table_schema = 'dbo'                         , table_name = 'sor'                         , column_name = 'surveyid' )     begin         print 'the colmun surveyid exists in sor table'          goto removeunwanteddata     end  alter table [dbo].[sor] add surveyid int null if @@error <> 0  begin     print 'error: ' + cast(@@error varchar(10)) + '. failed add surveyid column sor table.'     goto errorscript  end   print 'surveyid column added sor table'  removeunwanteddata: -- remove contractid = 0 - invalid rows  delete [dbo].[sor] contractid = 0 if @@error <> 0  begin     print 'error: ' + cast(@@error varchar(10)) + '. failed delete contractid = 0 in sor table.'     goto errorscript  end  print 'removed rows in sor contractid = 0, if there any' -- create query populate surveyid   update [dbo].[sor]   set surveyid = vw.surveyid -- **<----error here!!!!!!!! --**   dbo.firstsurveyidforcontractid vw vw.contractid = dbo.sor.contractid if @@error <> 0  begin     print 'error: ' + cast(@@error varchar(10)) + '. failed populate surveyid in sor table.'     goto errorscript  end   print 'surveyids populated in sor table'   -- check if surveyid can made not null  declare @numberofnullentriessors int  select @numberofnullentriessors = count(*) [dbo].[sor] surveyid null if @numberofnullentriessors > 0 begin     print 'there ' + cast(@numberofnullentriessors varchar(10)) + ' surveyids sors not set, check data before proceeding'     goto endscript end   print 'all surveyids set , surveyid can made not null.'  -- alter surveyid not null   alter table [dbo].[sor]  alter column surveyid int not null  if @@error <> 0  begin     print 'error: ' + cast(@@error varchar(10)) + ' failed set surveyid sor table not null'     goto errorscript  end   print 'surveyid not null'   -- create new foreign key relationships surveyid   alter table [dbo].[sor] add constraint [fk_sor_survey]   foreign key ([surveyid]) references [dbo].[survey] ([id])  if @@error <> 0  begin     print 'error: ' + cast(@@error varchar(10)) + ' failed set foreign key surveyid sor table.'     goto errorscript  end   print 'foreign key relationships set sor tables on surveyid'   -- remove contractid column  alter table [dbo].[sor] drop column contractid if @@error <> 0  begin     print 'error: ' + cast(@@error varchar(10)) + ' failed drop contractid column sor table.'     goto errorscript  end   print 'contractid column dropped sor tables'   goto successscript   errorscript:  print 'errorscript started'  rollback transaction  print 'errorscript completed'  goto endscript   successscript:  print 'successscript started'  commit transaction  print 'successscript completed'  endscript:  print 'end of script' 

use go after alter table statement , before update statement.

signals end of batch of transact-sql statements sql server utilities.

also, because transaction cannot span more 1 batch, need break transaction 2 transactions. alternative using goto error handling in situation use try/catch each transaction follows:

-- first batch begin transaction;  begin try     ...     alter table [dbo].[sor]     add surveyid int null     ... end try begin catch     if @@trancount > 0         rollback transaction; end catch;  if @@trancount > 0     commit transaction; go  -- second batch begin transaction;  begin try     ...     update [dbo].[sor]      set surveyid = vw.surveyid -- **<----error here!!!!!!!! --**     dbo.firstsurveyidforcontractid vw vw.contractid = dbo.sor.contractid     ... end try begin catch     if @@trancount > 0         rollback transaction; end catch;  if @@trancount > 0     commit transaction; go