Alter column in SQL Server

Question

What is correct syntax for an ALTER statement to add a default value to an existing column   I can add new column with no errors   ALTER TABLE tb TableName ADD Record Status varchar 20    But If I try to alter existing column to apply default value by using the following statement   ALTER TABLE tb TableName  ALTER COLUMN Record Status VARCHAR 20  NOT NULL DEFAULT      or  ALTER TABLE tb TableName  ALTER Record Status VARCHAR 20  NOT NULL DEFAULT      I have get an error       Incorrect syntax near  Record Status

User · Accepted Answer

I think you want this syntax   ALTER TABLE tb TableName   add constraint cnt Record Status Default    for Record Status   Based on some of your comments  I am going to guess that you might already have null values in your table which is causing the alter of the column to not null to fail   If that is the case  then you should run an UPDATE first  Your script will be   update tb TableName set Record Status       where Record Status is null  ALTER TABLE tb TableName ALTER COLUMN Record Status VARCHAR 20  NOT NULL  ALTER TABLE tb TableName ADD CONSTRAINT DEF Name DEFAULT    FOR Record Status   See SQL Fiddle with demo

User · Answer

Try this one     ALTER TABLE tb TableName ALTER COLUMN Record Status VARCHAR 20  NOT NULL  ALTER TABLE tb TableName ADD CONSTRAINT DEF Name DEFAULT    FOR Record Status

User · Answer

To set a default value to a column  try this   ALTER TABLE tb TableName ALTER COLUMN Record Status SET DEFAULT  default value

[sql] Alter column in SQL Server

Examples related to sql

Examples related to sql-server-2008