Add a Column With a Default Value to an Existing Table in SQL Server

Add a column with a default value to an existing table in SQL Server

Syntax:

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

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:

If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate

    a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:

The WITH VALUES is only needed when your Column is Nullable

    and you want the Default Value used for Existing Records.

If your Column is NOT NULL, then it will automatically use the Default Value

    for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:

If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.

If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),

    then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.

Special Thanks to:

    @Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Adding a column with a Default value?

ALTER TABLE PERSON
ADD IS_ACTIVE VARCHAR2(1) DEFAULT 'N'

If you want, you can add a NOT NULL constraint:

ALTER TABLE PERSON
ADD IS_ACTIVE VARCHAR2(1) DEFAULT 'N' NOT NULL

How to set a default value for an existing column

This will work in SQL Server:

ALTER TABLE Employee ADD CONSTRAINT DF_SomeName DEFAULT N'SANDNES' FOR CityBorn;

SQL server query to add column with default value

As I expected, updating my SQL Server Management Studio to version 17.8.1 solved my issue.

After updating, I was able to run this command without any problem.

How do I add a column, with a default value, to an existing table in SQL Server 2008 or 2012?

ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

Add a column with a default value to an existing table in SQL Server

Add a column to a table with a default value equal to the value of an existing column

Try this:

ALTER TABLE tablename ADD newcolumn type NOT NULL DEFAULT (0)
Go
Update tablename SET newcolumn = oldcolumn Where newcolumn = 0
Go

Add a column with a default value to an existing table in SQL Server

Syntax:

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

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:

If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate

    a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:

The WITH VALUES is only needed when your Column is Nullable

    and you want the Default Value used for Existing Records.

If your Column is NOT NULL, then it will automatically use the Default Value

    for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:

If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.

If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),

    then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.

Special Thanks to:

    @Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Conditionally add a column with a default value to an existing table in SQL Server

You may try this. There are basically 2 approach to achieve this.

Adding Column in table schema

As I understand you want to add a column in table schema as given link shared by you. You may use case statement to add column for calculated values.

EDIT : Correcting Syntax Error

alter table yourtable
    add AgeGroup as (case 
                        when Age < 2 then 'New Born'
                        when Age < 19 then 'Teen'
                        when Age < 45 then 'Young'
                        when Age > 60 then 'Adult'
                        else 'Invalid' 
                     end);

Or

Create view

You may create a view for same and use it wherever you needed.

Create View TableResult 
As
Select Id, Age, case Age 
                         WHEN Age < 2 then 'New Born'
                         WHEN Age < 19 then 'Teen'
                         WHEN Age < 45 then 'Young'
                         WHEN Age > 60 then 'Adult'
                         else 'Invalid' 
                     end as AgeGroup
End

How can I add a column in a table with a default value

I think you actually want a computed column?

One where the database always calculates it for you, and you never supply the value yourself?

alter table ETUDIANT add age as (datediff(year, sysdatetime(), DateN)) persisted;

https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver15

EDIT:

Or, perhaps a trigger?

CREATE TRIGGER trg_ETUDIANT_AfterInsert
  ON ETUDIANT 
AFTER INSERT
AS
  UPDATE ETUDIANT AS t
  SET t.age = t.datediff(year, sysdatetime(), DateN)
  FROM Inserted AS i
  WHERE t.PK = i.PK;
        -- Where PK is whatever unique key(s) exist on your table

Add a column with a default value to an existing table in postgresql

Referencing the most recent docs, this operation can be done using two statements.

1. Adds the column with the old default value

ALTER TABLE my_table ADD COLUMN description varchar(100) DEFAULT 'A1';

2. Modifies the column to use a different default value

ALTER TABLE my_table ALTER COLUMN description SET DEFAULT 'B2'

A full reproducible sample has been included below:

CREATE TABLE my_table (
  "name" VARCHAR(5),
  "work" VARCHAR(9)
);

INSERT INTO my_table
  ("name", "work")
VALUES
  ('bob', 'fireman'),
  ('carl', 'teacher'),
  ('alice', 'policeman');

---

Query #1

select * from my_table;

---