Adding a New SQL Column with a Default Value

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

SQL Server 2017 add new column with default value from another column in the same table

The error clearly tells you: if you add a column with NOT NULL to a table that already has data in it, you MUST include a DEFAULT clause to define the default values for the newly added column - you are not doing that....

So try this:

ALTER TABLE [dbo].[TableA] 
    ADD [Weight2] FLOAT NOT NULL
    CONSTRAINT DF_TableA_Weight2 DEFAULT(0);

and then you can update Weight2 to get the same values as in Weight1:

UPDATE dbo.TableA
SET Weight2 = Weight1

Add new column with default value, not for existing rows

Try doing something like:

SQL> create table testtab
(
id number,
col1 varchar2(10)
)
Table created.
SQL> insert into testtab(id, col1) values (1,'TEST1')
1 row created.
SQL> insert into testtab(id, col1) values (2,'TEST2')
1 row created.
SQL> commit
Commit complete.
SQL> alter table testtab add col2 varchar2(10)
Table altered.
SQL> alter table testtab modify col2 default 'DEFAULT'
Table altered.
SQL> select * from testtab

        ID COL1       COL2      
---------- ---------- ----------
         1 TEST1                
         2 TEST2                

2 rows selected.
SQL> insert into testtab(id, col1) values (3,'TEST3')
1 row created.
SQL> select * from testtab

        ID COL1       COL2      
---------- ---------- ----------
         1 TEST1                
         2 TEST2                
         3 TEST3      DEFAULT   

3 rows selected.

Note that per the Oracle docs, if you use the add column clause of alter table command, it will add the specified default value to existing rows:

If you specify the DEFAULT clause for a nullable column, then the
default value is added to existing rows as part of this ALTER TABLE
statement, and any update triggers defined on the table are fired.
This behavior also results if you change a NOT NULL column with a
default value to be nullable.

Adding a new SQL column with a default value

Try this:

ALTER TABLE table1 ADD COLUMN foo INT DEFAULT 0;

From the documentation that you linked to:

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
   alter_specification [, alter_specification] ...

alter_specification:
    ...
    ADD [COLUMN] (col_name column_definition,...)
    ...

To find the syntax for column_definition search a bit further down the page:

column_definition clauses use the same syntax for ADD and CHANGE as for CREATE TABLE. See Section 12.1.17, “CREATE TABLE Syntax”.

And from the linked page:

column_definition:  
   data_type [NOT NULL | NULL] [DEFAULT default_value]
   [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]  
   [COMMENT 'string']  
   [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]  
   [STORAGE {DISK|MEMORY|DEFAULT}]  
   [reference_definition]  

Notice the word DEFAULT there.

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.

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;

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

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.