How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?
This question is related to
sql
sql-server
sql-server-2005
sql-server-2000
The answer is
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]
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 & 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.
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
--Adding Value with Default Value
ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
GO
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.
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 < 1000000
BREAK;
END
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]
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();''' ;
ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'SNUGGLES'
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
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 & 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.
ALTER TABLE Table1 ADD Col3 INT NOT NULL DEFAULT(0)
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))
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;
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!
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
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
Use:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
Reference: ALTER TABLE (Transact-SQL) (MSDN)
The most basic version with two lines only
ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0
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->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'or0or whatever value you want in Default Value or Binding field as pictured below:
ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'SNUGGLES'
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
The most basic version with two lines only
ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0
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
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
ALTER TABLE <table name>
ADD <new column name> <data type> NOT NULL
GO
ALTER TABLE <table name>
ADD CONSTRAINT <constraint name> DEFAULT <default value> FOR <new column name>
GO
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
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.
ALTER table dataset.tablename ADD column_current_ind integer DEFAULT 0
ALTER TABLE <table name>
ADD <new column name> <data type> NOT NULL
GO
ALTER TABLE <table name>
ADD CONSTRAINT <constraint name> DEFAULT <default value> FOR <new column name>
GO
Try with the below query:
ALTER TABLE MyTable
ADD MyNewColumn DataType DEFAULT DefaultValue
This will add a new column into the Table.
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))
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
SQL Server + Alter Table + Add Column + Default Value uniqueidentifier...
ALTER TABLE [TABLENAME] ADD MyNewColumn INT not null default 0 GO
Example:
ALTER TABLE tes
ADD ssd NUMBER DEFAULT '0';
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.
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
Try this
ALTER TABLE Product
ADD ProductID INT NOT NULL DEFAULT(1)
GO
ALTER TABLE Table1 ADD Col3 INT NOT NULL DEFAULT(0)
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
Try this
ALTER TABLE Product
ADD ProductID INT NOT NULL DEFAULT(1)
GO
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->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'or0or whatever value you want in Default Value or Binding field as pictured below:
ALTER TABLE <table name>
ADD <new column name> <data type> NOT NULL
GO
ALTER TABLE <table name>
ADD CONSTRAINT <constraint name> DEFAULT <default value> FOR <new column name>
GO
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
ALTER table dataset.tablename ADD column_current_ind integer DEFAULT 0
ALTER TABLE <YOUR_TABLENAME>
ADD <YOUR_COLUMNNAME> <DATATYPE> <NULL|NOT NULL>
ADD CONSTRAINT <CONSTRAINT_NAME> ----OPTIONAL
DEFAULT <DEFAULT_VALUE>
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)
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
ALTER TABLE <table name>
ADD <new column name> <data type> NOT NULL
GO
ALTER TABLE <table name>
ADD CONSTRAINT <constraint name> DEFAULT <default value> FOR <new column name>
GO
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.
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';
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]
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!
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';
ALTER TABLE ADD ColumnName {Column_Type} Constraint
The MSDN article ALTER TABLE (Transact-SQL) has all of the alter table syntax.
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
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.
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.
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.
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.
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.
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;
--Adding Value with Default Value
ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
GO
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.
ALTER TABLE ADD ColumnName {Column_Type} Constraint
The MSDN article ALTER TABLE (Transact-SQL) has all of the alter table syntax.
Try with the below query:
ALTER TABLE MyTable
ADD MyNewColumn DataType DEFAULT DefaultValue
This will add a new column into the Table.
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.
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.
--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]
You can use this query:
ALTER TABLE tableName ADD ColumnName datatype DEFAULT DefaultValue;
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
Example:
ALTER TABLE [Employees] ADD Seniority int not null default 0 GO
Use:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
Reference: ALTER TABLE (Transact-SQL) (MSDN)
--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]
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 < 1000000
BREAK;
END
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.
Example:
ALTER TABLE tes
ADD ssd NUMBER DEFAULT '0';
You can use this query:
ALTER TABLE tableName ADD ColumnName datatype DEFAULT DefaultValue;
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]
ALTER TABLE ADD ColumnName {Column_Type} Constraint
The MSDN article ALTER TABLE (Transact-SQL) has all of the alter table syntax.
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.
Example:
ALTER TABLE [Employees] ADD Seniority int not null default 0 GO
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();''' ;
ALTER TABLE <YOUR_TABLENAME>
ADD <YOUR_COLUMNNAME> <DATATYPE> <NULL|NOT NULL>
ADD CONSTRAINT <CONSTRAINT_NAME> ----OPTIONAL
DEFAULT <DEFAULT_VALUE>
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)
Source: Stackoverflow.com