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

Question

How can I add a column with a default value to an existing table in SQL Server 2000   SQL Server 2005

User · Answer

IF NOT EXISTS       SELECT   FROM INFORMATION SCHEMA COLUMNS     WHERE TABLE NAME   TABLENAME  AND COLUMN NAME    COLUMNNAME    BEGIN     ALTER TABLE TABLENAME ADD COLUMNNAME Nvarchar MAX  Not Null default END

User · Answer

ALTER TABLE Protocols ADD ProtocolTypeID int NOT NULL DEFAULT 1  GO   The inclusion of the DEFAULT fills the column in existing rows with the default value  so the NOT NULL constraint is not violated

User · Answer

Well  I now have some modification to my previous answer  I have noticed that none of the answers mentioned IF NOT EXISTS  So I am going to provide a new solution of it as I have faced some problems altering the table   IF NOT EXISTS  SELECT   FROM INFORMATION SCHEMA columns WHERE table name    TaskSheet  AND column name    IsBilledToClient   BEGIN ALTER TABLE dbo TaskSheet ADD  IsBilledToClient bit NOT NULL DEFAULT   1   END GO   Here TaskSheet is the particular table name and IsBilledToClient is the new column which you are going to insert and 1 the default value  That means in the new column what will be the value of the existing rows  therefore one will be set automatically there  However  you can change as you wish with the respect of the column type like I have used BIT  so I put in default value 1   I suggest the above system  because I have faced a problem  So what is the problem  The problem is  if the IsBilledToClient column does exists in the table table then if you execute only the portion of the code given below you will see an error in the SQL server Query builder  But if it does not exist then for the first time there will be no error when executing   ALTER TABLE  TABLENAME  ADD  COLUMNNAME   TYPE   NULL NOT NULL  CONSTRAINT  CONSTRAINT NAME  DEFAULT  DEFAULT VALUE   WITH VALUES

User · Answer

To add a column to an existing database table with a default value  we can use   ALTER TABLE  dbo table name      ADD  Column Name  BIT NOT NULL Default   0     Here is another way to add a column to an existing database table with a default value   A much more thorough SQL script to add a column with a default value is below including checking if the column exists before adding it also checkin the constraint and dropping it if there is one  This script also names the constraint so we can have a nice naming convention  I like DF   and if not SQL will give us a constraint with a name which has a randomly generated number  so it s nice to be able to name the constraint too   ------------------------------------------------------------------------- -- Drop COLUMN -- Name of Column  Column EmployeeName -- Name of Table  table Emplyee -------------------------------------------------------------------------- IF EXISTS               SELECT 1             FROM INFORMATION SCHEMA COLUMNS             WHERE TABLE NAME    table Emplyee                AND COLUMN NAME    Column EmployeeName                   BEGIN          IF EXISTS   SELECT 1                     FROM sys default constraints                     WHERE object id   OBJECT ID   dbo   DF table Emplyee Column EmployeeName                          AND parent object id   OBJECT ID   dbo   table Emplyee                                    BEGIN                 ------  DROP Contraint                  ALTER TABLE  dbo   table Emplyee  DROP CONSTRAINT  DF table Emplyee Column EmployeeName              PRINT   DF table Emplyee Column EmployeeName  was dropped              END      --    -----   DROP Column   -----------------------------------------------------------------         ALTER TABLE  dbo  table Emplyee             DROP COLUMN Column EmployeeName         PRINT  Column Column EmployeeName in images table was dropped      END  -------------------------------------------------------------------------- -- ADD  COLUMN Column EmployeeName IN table Emplyee table -------------------------------------------------------------------------- IF NOT EXISTS                   SELECT 1                 FROM INFORMATION SCHEMA COLUMNS                 WHERE TABLE NAME    table Emplyee                    AND COLUMN NAME    Column EmployeeName                       BEGIN     ----- ADD Column  amp  Contraint         ALTER TABLE dbo table Emplyee             ADD Column EmployeeName BIT   NOT NULL             CONSTRAINT  DF table Emplyee Column EmployeeName   DEFAULT  0          PRINT  Column  DF table Emplyee Column EmployeeName  in table Emplyee table was Added          PRINT  Contraint  DF table Emplyee Column EmployeeName  was Added       END  GO   These are two ways to add a column to an existing database table with a default value

User · Answer

ALTER TABLE Protocols ADD ProtocolTypeID int NOT NULL DEFAULT 1  GO   The inclusion of the DEFAULT fills the column in existing rows with the default value  so the NOT NULL constraint is not violated

User · Answer

Example   ALTER TABLE tes  ADD ssd  NUMBER   DEFAULT  0

User · Answer

ALTER table dataset tablename ADD column current ind integer DEFAULT 0

User · Answer

When adding a nullable column  WITH VALUES will ensure that the specific DEFAULT value is applied to existing rows   ALTER TABLE table ADD column BIT     -- Demonstration with NULL-able column added CONSTRAINT Constraint name DEFAULT 0 WITH VALUES

User · Answer

ALTER TABLE  lt YOUR TABLENAME gt  ADD  lt YOUR COLUMNNAME gt   lt DATATYPE gt   lt NULL NOT NULL gt   ADD CONSTRAINT  lt CONSTRAINT NAME gt    ----OPTIONAL DEFAULT  lt DEFAULT VALUE gt    If you are not giving constrain name then sql server use default name for this   Example -  ALTER TABLE TEMP TABLENAME ADD COLUMN1 NUMERIC 10 0  NOT NULL ADD CONSTRAINT ABCDE   ----OPTIONAL DEFAULT  0

User · Answer

OFFLINE and ONLINE pertain to how to ALTER table performed on NDB Cluster Tables  NDB Cluster supports online ALTER TABLE operations using the ALGORITHM INPLACE syntax in MySQL NDB Cluster 7 3 and later  NDB Cluster also supports an older syntax specific to NDB that uses the ONLINE and OFFLINE keywords  These keywords are deprecated beginning with MySQL NDB Cluster 7 3  they continue to be supported in MySQL NDB Cluster 7 4 but are subject to removal in a future version of NDB Cluster   IGNORE pertains to how the ALTER statement will deal with duplicate value in the column that has newly added constraint UNIQUE  If IGNORE is not specified  ALTER will fail and not be applied  If IGNORE is specified  the first row of all duplicate rows is kept  the reset deleted and the ALTER applied   The ALTER SPECIFICATION would be what you are changing  what column or index you are adding  dropping or modifying  or what constraints you are applying on the column    ALTER  ONLINE   OFFLINE   IGNORE  TABLE tbl name        alter specification    alter specification           alter specification                      ADD  COLUMN   col name column definition                   Eg  ALTER TABLE table1 ADD COLUMN foo INT DEFAULT 0

User · Answer

SQL Server   Alter Table   Add Column   Default Value uniqueidentifier     ALTER TABLE  TABLENAME  ADD MyNewColumn INT not null default 0 GO

User · Answer

If you want to add multiple columns you can do it this way for example   ALTER TABLE YourTable     ADD Column1 INT NOT NULL DEFAULT 0          Column2 INT NOT NULL DEFAULT 1          Column3 VARCHAR 50  DEFAULT  Hello  GO

User · Answer

Try this  ALTER TABLE Product ADD ProductID INT NOT NULL DEFAULT 1  GO

User · Answer

This has a lot of answers  but I feel the need to add this extended method  This seems a lot longer  but it is extremely useful if you re adding a NOT NULL field to a table with millions of rows in an active database   ALTER TABLE  schemaName   tableName      ADD  columnName   datatype  NULL     CONSTRAINT  constraintName  DEFAULT  DefaultValue   UPDATE  schemaName   tableName      SET  columnName     DefaultValue      WHERE  columName  IS NULL  ALTER TABLE  schemaName   tableName      ALTER COLUMN  columnName   datatype  NOT NULL   What this will do is add the column as a nullable field and with the default value  update all fields to the default value  or you can assign more meaningful values   and finally it will change the column to be NOT NULL   The reason for this is if you update a large scale table and add a new not null field it has to write to every single row and hereby will lock out the entire table as it adds the column and then writes all the values   This method will add the nullable column which operates a lot faster by itself  then fills the data before setting the not null status   I ve found that doing the entire thing in one statement will lock out one of our more active tables for 4-8 minutes and quite often I have killed the process  This method each part usually takes only a few seconds and causes minimal locking   Additionally  if you have a table in the area of billions of rows it may be worth batching the update like so   WHILE 1 1 BEGIN     UPDATE TOP  1000000   schemaName   tableName          SET  columnName     DefaultValue          WHERE  columName  IS NULL      IF   ROWCOUNT  lt  1000000         BREAK  END

User · Answer

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  nbsp   nbsp  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  nbsp   nbsp  and you want the Default Value used for Existing Records  If your Column is NOT NULL  then it will automatically use the Default Value  nbsp   nbsp  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    nbsp   nbsp  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   nbsp   nbsp   Yatrix   WalterStabosz   YahooSerious  and  StackMan for their Comments

User · Answer

Example   ALTER TABLE tes  ADD ssd  NUMBER   DEFAULT  0

User · Answer

IF NOT EXISTS       SELECT   FROM INFORMATION SCHEMA COLUMNS     WHERE TABLE NAME   TABLENAME  AND COLUMN NAME    COLUMNNAME    BEGIN     ALTER TABLE TABLENAME ADD COLUMNNAME Nvarchar MAX  Not Null default END

User · Answer

Beware when the column you are adding has a NOT NULL constraint  yet does not have a DEFAULT constraint  value   The ALTER TABLE statement will fail in that case if the table has any rows in it  The solution is to either remove the NOT NULL constraint from the new column  or provide a DEFAULT constraint for it

User · Answer

Try with the below query   ALTER TABLE MyTable ADD MyNewColumn DataType DEFAULT DefaultValue   This will add a new column into the Table

User · Answer

Add a new column to a table   ALTER TABLE  table  ADD Column1 Datatype   For example   ALTER TABLE  test  ADD ID Int   If the user wants to make it auto incremented then   ALTER TABLE  test  ADD ID Int IDENTITY 1 1  NOT NULL

User · Answer

ALTER TABLE ADD ColumnName  Column Type  Constraint   The MSDN article ALTER TABLE  Transact-SQL  has all of the alter table syntax

User · Answer

This can be done in the SSMS GUI as well  I show a default date below but the default value can be whatever  of course   Put your table in design view  Right click on the table in object explorer- gt Design  Add a column to the table  or click on the column you want to update if it already exists  In Column Properties below  enter  getdate    or  abc  or 0 or whatever value you want in Default Value or Binding field as pictured below

User · Answer

step-1  FIRST YOU HAVE TO ALTER TABLE WITH ADD a FIELD  alter table table name add field field name data type   step-2 CREATE DEFAULT  USE data base name  GO CREATE DEFAULT default name AS  default value     step-3 THEN YOU HAVE TO EXECUTE THIS PROCEDURE  exec sp bindefault  default name     schema name table name field name    example -   USE master  GO EXEC sp bindefault  today    HumanResources Employee HireDate

User · Answer

ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR 200  DEFAULT  SNUGGLES

User · Answer

This can be done in the SSMS GUI as well  I show a default date below but the default value can be whatever  of course   Put your table in design view  Right click on the table in object explorer- gt Design  Add a column to the table  or click on the column you want to update if it already exists  In Column Properties below  enter  getdate    or  abc  or 0 or whatever value you want in Default Value or Binding field as pictured below

User · Answer

In SQL Server 2008-R2  I go to the design mode - in a test database - and add my two columns using the designer and made the settings with the GUI  and then the infamous Right-Click gives the option  Generate Change Script    Bang up pops a little window with  you guessed it  the properly formatted guaranteed-to-work change script  Hit the easy button

User · Answer

--Adding Value with Default Value ALTER TABLE TestTable ADD ThirdCol INT NOT NULL DEFAULT 0  GO

User · Answer

If the default is Null  then    In SQL Server  open the tree of the targeted table Right click  Columns      New Column Type the column Name  Select Type  and Check the Allow Nulls Checkbox From the Menu Bar  click Save   Done

User · Answer

You can use this query   ALTER TABLE tableName ADD ColumnName datatype DEFAULT DefaultValue

User · Answer

The most basic version with two lines only  ALTER TABLE MyTable ADD MyNewColumn INT NOT NULL DEFAULT 0

User · Answer

This is for SQL Server   ALTER TABLE TableName ADD ColumnName  type  -- NULL OR NOT NULL DEFAULT  default value  WITH VALUES   Example   ALTER TABLE Activities ADD status int NOT NULL DEFAULT  0  WITH VALUES   If you want to add constraints then   ALTER TABLE Table 1 ADD row3 int NOT NULL CONSTRAINT CONSTRAINT NAME DEFAULT  0  WITH VALUES

User · Answer

SQL Server   Alter Table   Add Column   Default Value uniqueidentifier   ALTER TABLE Product  ADD ReferenceID uniqueidentifier not null  default  cast cast 0 as binary  as uniqueidentifier

User · Answer

Use   -- Add a column with a default DateTime   -- to capture when each record is added   ALTER TABLE myTableName   ADD RecordAddedDate SMALLDATETIME NULL DEFAULT  GETDATE      GO

User · Answer

ALTER TABLE ADD ColumnName  Column Type  Constraint   The MSDN article ALTER TABLE  Transact-SQL  has all of the alter table syntax

User · Answer

ALTER TABLE Table1 ADD Col3 INT NOT NULL DEFAULT 0

User · Answer

The most basic version with two lines only  ALTER TABLE MyTable ADD MyNewColumn INT NOT NULL DEFAULT 0

User · Answer

SQL Server   Alter Table   Add Column   Default Value uniqueidentifier     ALTER TABLE  TABLENAME  ADD MyNewColumn INT not null default 0 GO

User · Answer

ALTER TABLE  lt table name gt   ADD  lt new column name gt   lt data type gt  NOT NULL GO ALTER TABLE  lt table name gt   ADD CONSTRAINT  lt constraint name gt  DEFAULT  lt default value gt  FOR  lt new column name gt  GO

User · Answer

Alternatively  you can add a default without having to explicitly name the constraint   ALTER TABLE  schema   tablename  ADD  DEFAULT   0   FOR  columnname    If you have an issue with existing default constraints when creating this constraint then they can be removed by   alter table  schema   tablename  drop constraint  constraintname

User · Answer

ALTER table dataset tablename ADD column current ind integer DEFAULT 0

User · Answer

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  nbsp   nbsp  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  nbsp   nbsp  and you want the Default Value used for Existing Records  If your Column is NOT NULL  then it will automatically use the Default Value  nbsp   nbsp  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    nbsp   nbsp  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   nbsp   nbsp   Yatrix   WalterStabosz   YahooSerious  and  StackMan for their Comments

User · Answer

This is for SQL Server   ALTER TABLE TableName ADD ColumnName  type  -- NULL OR NOT NULL DEFAULT  default value  WITH VALUES   Example   ALTER TABLE Activities ADD status int NOT NULL DEFAULT  0  WITH VALUES   If you want to add constraints then   ALTER TABLE Table 1 ADD row3 int NOT NULL CONSTRAINT CONSTRAINT NAME DEFAULT  0  WITH VALUES

User · Answer

--Adding New Column with Default Value ALTER TABLE TABLENAME  ADD COLUMNNAME DATATYPE NULL NOT NULL DEFAULT  DEFAULT VALUE    OR  --Adding CONSTRAINT And Set Default Value on Column ALTER TABLE TABLENAME ADD  CONSTRAINT  CONSTRAINT Name   DEFAULT   DEFAULT VALUE  FOR  COLUMNNAME

User · Answer

SQL Server   Alter Table   Add Column   Default Value uniqueidentifier   ALTER TABLE Product  ADD ReferenceID uniqueidentifier not null  default  cast cast 0 as binary  as uniqueidentifier

User · Answer

Well  I now have some modification to my previous answer  I have noticed that none of the answers mentioned IF NOT EXISTS  So I am going to provide a new solution of it as I have faced some problems altering the table   IF NOT EXISTS  SELECT   FROM INFORMATION SCHEMA columns WHERE table name    TaskSheet  AND column name    IsBilledToClient   BEGIN ALTER TABLE dbo TaskSheet ADD  IsBilledToClient bit NOT NULL DEFAULT   1   END GO   Here TaskSheet is the particular table name and IsBilledToClient is the new column which you are going to insert and 1 the default value  That means in the new column what will be the value of the existing rows  therefore one will be set automatically there  However  you can change as you wish with the respect of the column type like I have used BIT  so I put in default value 1   I suggest the above system  because I have faced a problem  So what is the problem  The problem is  if the IsBilledToClient column does exists in the table table then if you execute only the portion of the code given below you will see an error in the SQL server Query builder  But if it does not exist then for the first time there will be no error when executing   ALTER TABLE  TABLENAME  ADD  COLUMNNAME   TYPE   NULL NOT NULL  CONSTRAINT  CONSTRAINT NAME  DEFAULT  DEFAULT VALUE   WITH VALUES

User · Answer

OFFLINE and ONLINE pertain to how to ALTER table performed on NDB Cluster Tables  NDB Cluster supports online ALTER TABLE operations using the ALGORITHM INPLACE syntax in MySQL NDB Cluster 7 3 and later  NDB Cluster also supports an older syntax specific to NDB that uses the ONLINE and OFFLINE keywords  These keywords are deprecated beginning with MySQL NDB Cluster 7 3  they continue to be supported in MySQL NDB Cluster 7 4 but are subject to removal in a future version of NDB Cluster   IGNORE pertains to how the ALTER statement will deal with duplicate value in the column that has newly added constraint UNIQUE  If IGNORE is not specified  ALTER will fail and not be applied  If IGNORE is specified  the first row of all duplicate rows is kept  the reset deleted and the ALTER applied   The ALTER SPECIFICATION would be what you are changing  what column or index you are adding  dropping or modifying  or what constraints you are applying on the column    ALTER  ONLINE   OFFLINE   IGNORE  TABLE tbl name        alter specification    alter specification           alter specification                      ADD  COLUMN   col name column definition                   Eg  ALTER TABLE table1 ADD COLUMN foo INT DEFAULT 0

User · Answer

ALTER TABLE tbl table ADD int column int NOT NULL DEFAULT 0    From this query you can add a column of datatype integer with default value 0

User · Answer

In SQL Server 2008-R2  I go to the design mode - in a test database - and add my two columns using the designer and made the settings with the GUI  and then the infamous Right-Click gives the option  Generate Change Script    Bang up pops a little window with  you guessed it  the properly formatted guaranteed-to-work change script  Hit the easy button

User · Answer

ALTER TABLE ADD ColumnName  Column Type  Constraint   The MSDN article ALTER TABLE  Transact-SQL  has all of the alter table syntax

User · Answer

When adding a nullable column  WITH VALUES will ensure that the specific DEFAULT value is applied to existing rows   ALTER TABLE table ADD column BIT     -- Demonstration with NULL-able column added CONSTRAINT Constraint name DEFAULT 0 WITH VALUES

User · Answer

You can do the thing with T-SQL in the following way    ALTER TABLE  TABLENAME   ADD  COLUMNNAME   TYPE   NULL NOT NULL   CONSTRAINT  CONSTRAINT NAME  DEFAULT  DEFAULT VALUE    As well as you can use SQL Server Management Studio also by right clicking table in the Design menu  setting the default value to table   And furthermore  if you want to add the same column  if it does not exists  to all tables in database  then use    USE AdventureWorks   EXEC sp msforeachtable  PRINT   ALTER TABLE   ADD Date Created DATETIME DEFAULT GETDATE

User · Answer

ALTER TABLE  lt table name gt   ADD  lt new column name gt   lt data type gt  NOT NULL GO ALTER TABLE  lt table name gt   ADD CONSTRAINT  lt constraint name gt  DEFAULT  lt default value gt  FOR  lt new column name gt  GO

User · Answer

You can use this query   ALTER TABLE tableName ADD ColumnName datatype DEFAULT DefaultValue

User · Answer

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  nbsp   nbsp  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  nbsp   nbsp  and you want the Default Value used for Existing Records  If your Column is NOT NULL  then it will automatically use the Default Value  nbsp   nbsp  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    nbsp   nbsp  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   nbsp   nbsp   Yatrix   WalterStabosz   YahooSerious  and  StackMan for their Comments

User · Answer

This can be done by the below code   CREATE TABLE TestTable      FirstCol INT NOT NULL      GO     ------------------------------     -- Option 1     ------------------------------     -- Adding New Column     ALTER TABLE TestTable     ADD SecondCol INT     GO     -- Updating it with Default     UPDATE TestTable     SET SecondCol   0     GO     -- Alter     ALTER TABLE TestTable     ALTER COLUMN SecondCol INT NOT NULL     GO

User · Answer

step-1  FIRST YOU HAVE TO ALTER TABLE WITH ADD a FIELD  alter table table name add field field name data type   step-2 CREATE DEFAULT  USE data base name  GO CREATE DEFAULT default name AS  default value     step-3 THEN YOU HAVE TO EXECUTE THIS PROCEDURE  exec sp bindefault  default name     schema name table name field name    example -   USE master  GO EXEC sp bindefault  today    HumanResources Employee HireDate

User · Answer

Use   ALTER TABLE  TABLENAME   ADD  COLUMNNAME   TYPE   NULL NOT NULL   CONSTRAINT  CONSTRAINT NAME  DEFAULT  DEFAULT VALUE    Reference  ALTER TABLE  Transact-SQL   MSDN

User · Answer

This has a lot of answers  but I feel the need to add this extended method  This seems a lot longer  but it is extremely useful if you re adding a NOT NULL field to a table with millions of rows in an active database   ALTER TABLE  schemaName   tableName      ADD  columnName   datatype  NULL     CONSTRAINT  constraintName  DEFAULT  DefaultValue   UPDATE  schemaName   tableName      SET  columnName     DefaultValue      WHERE  columName  IS NULL  ALTER TABLE  schemaName   tableName      ALTER COLUMN  columnName   datatype  NOT NULL   What this will do is add the column as a nullable field and with the default value  update all fields to the default value  or you can assign more meaningful values   and finally it will change the column to be NOT NULL   The reason for this is if you update a large scale table and add a new not null field it has to write to every single row and hereby will lock out the entire table as it adds the column and then writes all the values   This method will add the nullable column which operates a lot faster by itself  then fills the data before setting the not null status   I ve found that doing the entire thing in one statement will lock out one of our more active tables for 4-8 minutes and quite often I have killed the process  This method each part usually takes only a few seconds and causes minimal locking   Additionally  if you have a table in the area of billions of rows it may be worth batching the update like so   WHILE 1 1 BEGIN     UPDATE TOP  1000000   schemaName   tableName          SET  columnName     DefaultValue          WHERE  columName  IS NULL      IF   ROWCOUNT  lt  1000000         BREAK  END

User · Answer

This can be done by the below code   CREATE TABLE TestTable      FirstCol INT NOT NULL      GO     ------------------------------     -- Option 1     ------------------------------     -- Adding New Column     ALTER TABLE TestTable     ADD SecondCol INT     GO     -- Updating it with Default     UPDATE TestTable     SET SecondCol   0     GO     -- Alter     ALTER TABLE TestTable     ALTER COLUMN SecondCol INT NOT NULL     GO

User · Answer

ALTER TABLE  lt YOUR TABLENAME gt  ADD  lt YOUR COLUMNNAME gt   lt DATATYPE gt   lt NULL NOT NULL gt   ADD CONSTRAINT  lt CONSTRAINT NAME gt    ----OPTIONAL DEFAULT  lt DEFAULT VALUE gt    If you are not giving constrain name then sql server use default name for this   Example -  ALTER TABLE TEMP TABLENAME ADD COLUMN1 NUMERIC 10 0  NOT NULL ADD CONSTRAINT ABCDE   ----OPTIONAL DEFAULT  0

User · Answer

Beware when the column you are adding has a NOT NULL constraint  yet does not have a DEFAULT constraint  value   The ALTER TABLE statement will fail in that case if the table has any rows in it  The solution is to either remove the NOT NULL constraint from the new column  or provide a DEFAULT constraint for it

User · Answer

If the default is Null  then    In SQL Server  open the tree of the targeted table Right click  Columns      New Column Type the column Name  Select Type  and Check the Allow Nulls Checkbox From the Menu Bar  click Save   Done

User · Answer

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  nbsp   nbsp  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  nbsp   nbsp  and you want the Default Value used for Existing Records  If your Column is NOT NULL  then it will automatically use the Default Value  nbsp   nbsp  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    nbsp   nbsp  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   nbsp   nbsp   Yatrix   WalterStabosz   YahooSerious  and  StackMan for their Comments

User · Answer

ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR 200  DEFAULT  SNUGGLES

User · Answer

You can do the thing with T-SQL in the following way    ALTER TABLE  TABLENAME   ADD  COLUMNNAME   TYPE   NULL NOT NULL   CONSTRAINT  CONSTRAINT NAME  DEFAULT  DEFAULT VALUE    As well as you can use SQL Server Management Studio also by right clicking table in the Design menu  setting the default value to table   And furthermore  if you want to add the same column  if it does not exists  to all tables in database  then use    USE AdventureWorks   EXEC sp msforeachtable  PRINT   ALTER TABLE   ADD Date Created DATETIME DEFAULT GETDATE

User · Answer

--Adding Value with Default Value ALTER TABLE TestTable ADD ThirdCol INT NOT NULL DEFAULT 0  GO

User · Answer

Example   ALTER TABLE  Employees  ADD Seniority int not null default 0 GO

User · Answer

ALTER TABLE  lt table name gt   ADD  lt new column name gt   lt data type gt  NOT NULL GO ALTER TABLE  lt table name gt   ADD CONSTRAINT  lt constraint name gt  DEFAULT  lt default value gt  FOR  lt new column name gt  GO

User · Answer

To add a column to an existing database table with a default value  we can use   ALTER TABLE  dbo table name      ADD  Column Name  BIT NOT NULL Default   0     Here is another way to add a column to an existing database table with a default value   A much more thorough SQL script to add a column with a default value is below including checking if the column exists before adding it also checkin the constraint and dropping it if there is one  This script also names the constraint so we can have a nice naming convention  I like DF   and if not SQL will give us a constraint with a name which has a randomly generated number  so it s nice to be able to name the constraint too   ------------------------------------------------------------------------- -- Drop COLUMN -- Name of Column  Column EmployeeName -- Name of Table  table Emplyee -------------------------------------------------------------------------- IF EXISTS               SELECT 1             FROM INFORMATION SCHEMA COLUMNS             WHERE TABLE NAME    table Emplyee                AND COLUMN NAME    Column EmployeeName                   BEGIN          IF EXISTS   SELECT 1                     FROM sys default constraints                     WHERE object id   OBJECT ID   dbo   DF table Emplyee Column EmployeeName                          AND parent object id   OBJECT ID   dbo   table Emplyee                                    BEGIN                 ------  DROP Contraint                  ALTER TABLE  dbo   table Emplyee  DROP CONSTRAINT  DF table Emplyee Column EmployeeName              PRINT   DF table Emplyee Column EmployeeName  was dropped              END      --    -----   DROP Column   -----------------------------------------------------------------         ALTER TABLE  dbo  table Emplyee             DROP COLUMN Column EmployeeName         PRINT  Column Column EmployeeName in images table was dropped      END  -------------------------------------------------------------------------- -- ADD  COLUMN Column EmployeeName IN table Emplyee table -------------------------------------------------------------------------- IF NOT EXISTS                   SELECT 1                 FROM INFORMATION SCHEMA COLUMNS                 WHERE TABLE NAME    table Emplyee                    AND COLUMN NAME    Column EmployeeName                       BEGIN     ----- ADD Column  amp  Contraint         ALTER TABLE dbo table Emplyee             ADD Column EmployeeName BIT   NOT NULL             CONSTRAINT  DF table Emplyee Column EmployeeName   DEFAULT  0          PRINT  Column  DF table Emplyee Column EmployeeName  in table Emplyee table was Added          PRINT  Contraint  DF table Emplyee Column EmployeeName  was Added       END  GO   These are two ways to add a column to an existing database table with a default value

User · Answer

Use   ALTER TABLE  TABLENAME   ADD  COLUMNNAME   TYPE   NULL NOT NULL   CONSTRAINT  CONSTRAINT NAME  DEFAULT  DEFAULT VALUE    Reference  ALTER TABLE  Transact-SQL   MSDN

User · Answer

In SQL Server  you can use below template   ALTER TABLE  tablename  ADD       columnname   datatype  DEFAULT  default value    For example  to add a new column  Column1  of data type int with default value   1 into an existing table  Table1    you can use below query   ALTER TABLE  Table1  ADD       Column1  INT DEFAULT 1

User · Answer

Try this  ALTER TABLE Product ADD ProductID INT NOT NULL DEFAULT 1  GO

User · Answer

Alternatively  you can add a default without having to explicitly name the constraint   ALTER TABLE  schema   tablename  ADD  DEFAULT   0   FOR  columnname    If you have an issue with existing default constraints when creating this constraint then they can be removed by   alter table  schema   tablename  drop constraint  constraintname

User · Answer

Use   -- Add a column with a default DateTime   -- to capture when each record is added   ALTER TABLE myTableName   ADD RecordAddedDate SMALLDATETIME NULL DEFAULT  GETDATE      GO

User · Answer

First create a table with name student   CREATE TABLE STUDENT  STUDENT ID INT NOT NULL    Add one column to it   ALTER TABLE STUDENT  ADD STUDENT NAME INT NOT NULL DEFAULT 0   SELECT    FROM STUDENT   The table is created and a column is added to an existing table with a default value

User · Answer

ALTER TABLE tbl table ADD int column int NOT NULL DEFAULT 0    From this query you can add a column of datatype integer with default value 0

User · Answer

Add a new column to a table   ALTER TABLE  table  ADD Column1 Datatype   For example   ALTER TABLE  test  ADD ID Int   If the user wants to make it auto incremented then   ALTER TABLE  test  ADD ID Int IDENTITY 1 1  NOT NULL

User · Answer

ALTER TABLE ADD ColumnName  Column Type  Constraint   The MSDN article ALTER TABLE  Transact-SQL  has all of the alter table syntax

User · Answer

ALTER TABLE Table1 ADD Col3 INT NOT NULL DEFAULT 0

User · Answer

First create a table with name student   CREATE TABLE STUDENT  STUDENT ID INT NOT NULL    Add one column to it   ALTER TABLE STUDENT  ADD STUDENT NAME INT NOT NULL DEFAULT 0   SELECT    FROM STUDENT   The table is created and a column is added to an existing table with a default value

User · Answer

Right click on the table name and click on Design  click under the last column name and enter Column Name  Data Type  Allow Nulls   Then in bottom of page set a default value or binding   something like  1  for string or 1 for int

User · Answer

If you want to add multiple columns you can do it this way for example   ALTER TABLE YourTable     ADD Column1 INT NOT NULL DEFAULT 0          Column2 INT NOT NULL DEFAULT 1          Column3 VARCHAR 50  DEFAULT  Hello  GO

User · Answer

Example   ALTER TABLE  Employees  ADD Seniority int not null default 0 GO

User · Answer

ALTER TABLE Protocols ADD ProtocolTypeID int NOT NULL DEFAULT 1  GO   The inclusion of the DEFAULT fills the column in existing rows with the default value  so the NOT NULL constraint is not violated

User · Answer

--Adding New Column with Default Value ALTER TABLE TABLENAME  ADD COLUMNNAME DATATYPE NULL NOT NULL DEFAULT  DEFAULT VALUE    OR  --Adding CONSTRAINT And Set Default Value on Column ALTER TABLE TABLENAME ADD  CONSTRAINT  CONSTRAINT Name   DEFAULT   DEFAULT VALUE  FOR  COLUMNNAME

User · Answer

ALTER TABLE  lt table name gt   ADD  lt new column name gt   lt data type gt  NOT NULL GO ALTER TABLE  lt table name gt   ADD CONSTRAINT  lt constraint name gt  DEFAULT  lt default value gt  FOR  lt new column name gt  GO

User · Answer

Try with the below query   ALTER TABLE MyTable ADD MyNewColumn DataType DEFAULT DefaultValue   This will add a new column into the Table

User · Answer

ALTER TABLE Protocols ADD ProtocolTypeID int NOT NULL DEFAULT 1  GO   The inclusion of the DEFAULT fills the column in existing rows with the default value  so the NOT NULL constraint is not violated

User · Answer

In SQL Server  you can use below template   ALTER TABLE  tablename  ADD       columnname   datatype  DEFAULT  default value    For example  to add a new column  Column1  of data type int with default value   1 into an existing table  Table1    you can use below query   ALTER TABLE  Table1  ADD       Column1  INT DEFAULT 1

User · Answer

Right click on the table name and click on Design  click under the last column name and enter Column Name  Data Type  Allow Nulls   Then in bottom of page set a default value or binding   something like  1  for string or 1 for int

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to sql-server-2000