5

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.

  • I assume you're not calling the column 'COLUMN', because that's a reserved keyword in SQL Server? – Mark Sinkinson Dec 15 '14 at 13:23
  • @MarkSinkinson no I'm not calling the column 'COLUMN', just did it for writing a simple example...but I get it...bad idea – Hugo Assanti Dec 15 '14 at 15:22
  • Also, you know there are no nested or autonomous transactions in SQL Server, right? @@trancount may increase but that probably doesn't mean what you think it means. Rollback will roll them all back. – Aaron Bertrand Dec 15 '14 at 15:30
  • @AaronBertrand didn't know that, thanks for the tip! – Hugo Assanti Dec 15 '14 at 15:51
7

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
  • 1
    Just to clarify, the column name is being checked at parse time, when the column doesn't yet exist. – Aaron Bertrand Dec 15 '14 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.