CODEWITHSUNDEEP
sql-serverdefaultalter
He llo
He llo

Reputation: 149

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

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?

Upvotes: 5

Views: 7537

Answers (2)

He llo
He llo

Reputation: 149

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'

Upvotes: 6

Roxy'Pro
Roxy'Pro

Reputation: 4444

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

Upvotes: -1

Related Questions