How to set default value to existing rows while adding new column with default value to table

Question

I have table with 100 rows and I am adding new column with default value as show below.

ALTER  TABLE AccountDetails
ADD    
   UpdatedOn      DATETIME    DEFAULT GETDATE(),
   UpdatedBy      VARCHAR(50) DEFAULT 'System'

After execution of this alter statement new columns will be added to table ... which is perfect! however values for the existing rows for these columns will NULL.

Is there anyways where it will have default value by default instead of the executing separate update statement for those column to update default value explicitly?

score 5 · Answer 1 · edited Apr 05 '17 at 19:58

5

I got the answer myself. If we put NOT NULL constraint as well in alter statement so while altering table to add new column, default value will be applied to new column and not null constraint will not be violated.

   ALTER  TABLE AccountDetails
   ADD    
   UpdatedOn    DATETIME    NOT NULL DEFAULT GETDATE(),
   UpdatedBy    VARCHAR(50) NOT NULL DEFAULT 'System'

edited Apr 05 '17 at 19:58

Brian Tompsett - 汤莱恩

5,753
72
57
129

answered Apr 05 '17 at 18:40

He llo

129
1
4

score -1 · Answer 2 · answered Apr 05 '17 at 18:36

-1

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]

answered Apr 05 '17 at 18:36

Roxy'Pro

4,216
9
40
102

3

Although this code may help to solve the problem, it doesn't explain _why_ and/or _how_ it answers the question. Providing this additional context would significantly improve its long-term value. Please [edit] your answer to add explanation, including what limitations and assumptions apply. – Toby Speight Apr 05 '17 at 21:54
https://stackoverflow.com/a/92123/6921898 - for context. – wardaddy Jul 27 '20 at 07:33

How to set default value to existing rows while adding new column with default value to table

2 Answers2