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

Martin Smith · Accepted Answer · 2012-09-18 08:03:32Z

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

edited Sep 18, 2012 at 8:03

answered Sep 18, 2012 at 7:51

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vlad Over a year ago

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.

Martin Smith Over a year ago

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)

Vlad Over a year ago

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 stackoverflow.com/questions/1293638/…

Collectives™ on Stack Overflow

Alter table then update

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related