8

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

Burhan Ali
  • 2,258
  • 1
  • 28
  • 38
Vlad
  • 2,739
  • 7
  • 49
  • 100

1 Answers1

18

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
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • What if I want to update several fields with different values from 0, like 1? Tried this query it works well, but still I want to use update afterwards. – Vlad Sep 18 '12 at 07:56
  • 2
    You can add multiple columns and default constraints in the same way. Apart from that it is often best to do DDL in a different batch from DML to avoid parsing issues (either by adding `GO` between the statements or by using `EXEC` depending on the context you need to do this) – Martin Smith Sep 18 '12 at 08:04
  • Wow thanks for the advice. Yes DDL should be separated from DML, always. In this case I needed simple script to finish simple task. Anyway I used this answer here with GO statement and It worked as I expected http://stackoverflow.com/questions/1293638/sql-alter-table-then-modify-values – Vlad Sep 18 '12 at 08:07