This post describes how to add new column to existing table in SQL Server by using Transact-SQL .
Syntax:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
{WITH VALUES}
Example:
ALTER TABLE Item
ADD StockQty INT NULL --Or NOT NULL.
CONSTRAINT DF_Item_StockQty --default constraint name
DEFAULT (0)--default value
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.
Lets review the above statement:
-
Itemis the target table name, to which I want to add the column. -
StockQtyis the new column name being added, with typeINT, also you can specify it asNullableorNOT NULL. -
DF_Item_StockQtyis the optional constraint name given to the default constraint, if you do not specify name here, it will auto-generate a name likeDF_Item_StockQty_6DF5C21B0A -
DEFAULT (0):0 is the default value for new column in this example. -
WITH VALUESis only needed when your new column isNullableand you want to update existing records with the provided default value (0 in this example). If your Column isNOT NULL, then it will automatically update existing records with the provided default value, whether you specifyWITH VALUESor not.
How Inserts work with a Default-Constraint:
-
If you insert a record into table
Itemand do not specifyStockQty's value, then it will be set to the default value0. -
If you insert a record and specify
StockQty's value asNULL(and your column allows nulls), then the Default-Constraint will not be used andNULLwill be inserted as the Value.
