Create a column and insert into it in a single transaction?

Question

Is it possible to create a column and insert values ​​into it during the same transaction? This is part of the update script. I found the following method online , but it does not work; I get an error: Invalid column name 'IndexNumber'. . I assume this is because the transaction has not yet created a column, so there is nothing to insert.

Relevant parts of my script:

 Print 'Beginning Upgrade' Begin Transaction -- -------------------------------------------------------------------- USE [MyDatabase]; /* Widgets now can be ordered and the order can be modified */ ALTER TABLE [dbo].[Widgets] ADD [IndexNumber] [int] NULL; DECLARE @ind INT SET @ind = 0 UPDATE [dbo].[Widgets] SET @ind = [IndexNumber] = @ind + 1; ALTER TABLE [dbo].[Widgets] ALTER COLUMN [IndexNumber] [int] NOT NULL; -- -------------------------------------------------------------------- Commit tran Print 'Upgrade completed' 

The reason [IndexNumber] not an identifier column is because it must be editable.

Answer 1

Another alternative, if you do not want to split the code into separate lots, is to use EXEC to create a nested area / lot:

 Print 'Beginning Upgrade' Begin Transaction -- -------------------------------------------------------------------- USE [MyDatabase]; /* Widgets now can be ordered and the order can be modified */ ALTER TABLE [dbo].[Widgets] ADD [IndexNumber] [int] NULL; EXEC('DECLARE @ind INT SET @ind = 0 UPDATE [dbo].[Widgets] SET @ind = [IndexNumber] = @ind + 1;'); ALTER TABLE [dbo].[Widgets] ALTER COLUMN [IndexNumber] [int] NOT NULL; -- -------------------------------------------------------------------- Commit tran Print 'Upgrade completed' 

The reason the source code does not work, because it tries to compile the entire batch before it is run, the compilation fails, and therefore it does not even start the transaction, even if it changes the table.

Answer 2

You cannot add a new column and use it in one batch. You need to insert GO between adding a column and using it.

The transaction is still OK (you can check this by changing Commit tran to rollback tran to see that no changes have been made).

 Print 'Beginning Upgrade' Begin Transaction -- -------------------------------------------------------------------- USE [MyDatabase]; /* Widgets now can be ordered and the order can be modified */ ALTER TABLE [dbo].[Widgets] ADD [IndexNumber] [int] NULL; GO DECLARE @ind INT SET @ind = 0 UPDATE [dbo].[Widgets] SET @ind = [IndexNumber] = @ind + 1; ALTER TABLE [dbo].[Widgets] ALTER COLUMN [IndexNumber] [int] NOT NULL; -- -------------------------------------------------------------------- Commit tran Print 'Upgrade completed' 

Answer 3

Calling 'go' after modifying a table

 USE [MyDatabase]; /* Widgets now can be ordered and the order can be modified */ ALTER TABLE [dbo].[Widgets] ADD [IndexNumber] [int] NULL; go DECLARE @ind INT SET @ind = 0 UPDATE [dbo].[Widgets] SET @ind = [IndexNumber] = @ind + 1; ALTER TABLE [dbo].[Widgets] ALTER COLUMN [IndexNumber] [int] NOT NULL; -- -------------------------------------------------------------------- 

Commit traffic