Alter table and update in sql server transactions

Question

I'm trying to do an alter table to add a new column on a table, then I would like to do some update on the existing rows in the table to add a value to the recently new added column and finally do a select * to check that everything is ok and then make a rollback of the entire thing.

Something like

declare @transactionNameA varchar(20);
declare @transactionNameB varchar(20);
select @transactionNameA = 'TransactionA';
select @transactionNameB = 'TransactionB';

begin transaction @transactionNameA
alter table A add Column int;

begin transaction @transactionNameB
update A set Column = 1 where ID=1;
select * from A;

rollback transaction @transactionNameB;
rollback transaction @transactionNameA;

When I write that code I get an error saying the the column doesn't exist

What am I doing wrong? Thanks for your time and help.

Answer 1

You have to break it up into batches because the [Column] does not exist since the statements are being parsed as one batch so it cannot see the new column, hence you receive the error.

Once you break it up into batches with GO statement the column will be added to the table and then your next batch updating the new column will be successful.

Full example:

DROP TABLE #A;
GO
CREATE TABLE #A (ID int IDENTITY(1,1), column1 varchar(1))
GO
INSERT INTO #A (column1)
VALUES('a'),('b'),('c')
GO

begin transaction
alter table #A add [Column] int;
go

begin transaction
update #A set [Column] = 1 where ID=1;
select * from #A;

rollback transaction;
GO

SELECT *
FROM #A