Let SQL wait until previous statement is done

Question

I have been searching around but I cannot find the correct answer, probably I search wrong because I don't know what to look for :)

Anyway, I have a TSQL with a begin and commit transaction. In the transaction I add some columns and also rename some columns. Just after the renames and added column statement i also run some update statements to load data into the newly created columns.

Now the problem is that for some reason the update gives an error that it cannot update the given column as it does not exist (YET???).

My idea is that the statement is still working out the rename and the adding of the columns but already goes ahead with the update statements. The table is very big and has a few million records so I can imagine it takes some time to add and rename the columns

If I run first the rename and add statements and than separate the update statements, it does work. So it has to do with some wait time.

Is it possible to make sql force to execute step by step and wait until the complete statement is done before going to the next?

Answer 1

If you modify columns (e.g. add them), you have to finish the batch before you can continue with updating them. Insert the GO keyword between table structure changes and updates.

To illustrate that, the following code won't work:

create table sometable(col1 int)

go

alter table sometable add col2 varchar(10)

insert into sometable(col2) values ('a')

But inserting go will make the insert recognise the new column

create table sometable(col1 int)

go

alter table sometable add col2 varchar(10)

go

insert into sometable(col2) values ('a')

---

If you do it in the code, you may want to create separate transaction for the structure changes and data migration. You can still wrap them it in one transaction for data integrity.