Alter table then update

Question

How can I achieve this in transact sql.

I want to add new column to existing table and then update it with some values. Is it possible to do it in one sql script or I should use separate scripts?

Here is a samples code

 ALTER TABLE my_table ADD my_new_column bit NULL;

 UPDATE my_table SET my_new_column = 0;

I know that I am doing writing while the column still doesn't exist so thats why these two lines are not working. But how to acheve this in one script, i.e use some delay or how to be sure the column is created and then write data to it?

I used IF EXISTS with select from the table but it doesn't work.

thanks

Answer 1

You can add the new column and populate it at the same time by adding a default and using the WITH VALUES clause. You can then drop the default at the end if no longer needed. This approach can be used for multiple columns as below.

ALTER TABLE [myTable]
ADD [my_new_column] [bit] NULL CONSTRAINT DF_TMP DEFAULT 0 ,
    [my_new_column2] [bit] NULL CONSTRAINT DF_TMP2 DEFAULT 1 WITH VALUES;

ALTER TABLE [myTable] DROP DF_TMP, DF_TMP2