Create column and insert into it within the same transaction?

Question

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

The 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 why [IndexNumber] is not an identity column is that it must be editable.

score 23 · Accepted Answer · answered May 06 '14 at 13:15

23

Another alternative, if you don't want to split the code into separate batches, is to use EXEC to create a nested scope/batch:

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 why the original code doesn't work because it tries to compile the entire batch before running it - the compilation fails and so it never even starts the transaction, let along alters the table.

answered May 06 '14 at 13:15

Damien_The_Unbeliever

234,701
27
340
448

Looks good. Will using an exec cause the statement to be performed outside the transaction or after it? As for your earlier comment about the actual upgrade not working, it might be the case but this is my only lead at the moment. – Joel Peltonen May 06 '14 at 13:32
@Nenotlep - no, it'll still be within the same transaction. – Damien_The_Unbeliever May 06 '14 at 13:55
This actually seems like the easiest way to go, which for me is pretty much synonymous with the best. Using this instead of GO I don't have to reorder my labels or rethink my script flow. Cheers! – Joel Peltonen May 07 '14 at 06:22

score 1 · Answer 2 · answered May 06 '14 at 12:07

1

You cannot add a new column and use it within the same batch. You need to insert GO between adding a column and using it.

Transaction is still ok (you can test that by changing Commit tran to rollback tran to see no changes were committed).

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'

answered May 06 '14 at 12:07

Szymon

42,577
16
96
114

This looks like the best solution. The only issue is that after beginning the transaction, I do a version check and if it fails, I do a `GOTO Failover;`. The failover label is actually declared a little after `Commit tran`, so the GOTO is in a different batch than the declaration --> fail. Any suggestions for that? – Joel Peltonen May 06 '14 at 13:12
Can you move that label before GOTO? – Szymon May 06 '14 at 13:15
It might be difficult.. Here is a larger paste of the upgrade script: http://pastebin.com/FwKimM66 – Joel Peltonen May 06 '14 at 13:33
I'm trying this within a `BEGIN TRY / END TRY` block, and the inclusion of `GO` throws syntax errors on the `TRY` of the end statement (and the semicolon of a random line in the middle)...so confused – drzaus Sep 29 '14 at 16:37
1

Please, be aware that you will be not able to `ROLLBACK` transaction with `GO` inside. `TRY`/`CATCH` will not work with `GO`. As **result** - there is no real `TRANSACTION` in this example. If you fail after `GO` - everything before `GO` will be saved in DB and not reverted because you have only `COMMIT TRANSACTION` here. – Bohdan Kuts Mar 18 '20 at 13:58

score 1 · Answer 3 · edited Apr 27 '18 at 09:41

Call 'go' after you alter table

USE [MyDatabase];

  /* Widgets now can be ordered and the order can be modified */
  ALTER TABLE [dbo].[Widgets] ADD [IndexNumber] [int] NULL;
GO

BEGIN TRAN;
    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;

Create column and insert into it within the same transaction?

3 Answers3

Linked