How to Add a Column with Values to Existing SQL Server Table - TSQL Tutorial / SQL Server Tutorial

We often have to add a new column to existing Table/s. Let's consider we have dbo.Customer table and we are US based company and we never did business outside USA. so we never need Country Column to our existing Table for Address as we only operator in USA. Now company has decided to expand the business and might have the customers from other countries. What we want to do , we want to add column to existing table with default value. All the existing values should be also updated to default value when we add this new column. Moving forward if no Country Name provided, It should take USA otherwise value provided.

--Create Sample Table without CountryName Column
Create table dbo.Customer (
CustomerId Int Identity(1,1),
CustomerName VARCHAR(100),
StreetAddress VARCHAR(100),
City VARCHAR(100),
State CHAR(2))

--Insert couple of Records in Sample Table
Insert into dbo.Customer
Select 'Aamir Shahzad','Test Street Address','Charlotte','NC'
Union
Select 'M Raza','Test Street Address','Charlotte','NC'

Add the new column Without Providing NOT NULL Constraint 

Alter Table dbo.Customer
ADD CountryName VARCHAR(50)
Default 'USA'

Select * From dbo.Customer

Fig 1: Add New Column to Existing SQL Server Table without Providing Not Null Constraint

In above query we did not mention the Not Null constraint for our CountryName column. SQL Server took that by default as Null. Also it did not update the newly added column with default values.

Add New Column With NOT NULL AND Default Constraint

 If we will provide Not Null constraint for the CountryColumn and also default value. The newly added column will be populated with default values as shown below.

Alter Table dbo.Customer
ADD CountryName VARCHAR(50) NOT NULL
Default 'USA'

Select * from  dbo.Customer

Fig 2: Add Not Null Constraint with Default Value To populate existing Column in Table

Now consider the last scenario, In which we want to keep our Column to accept Null values but populate existing records with default and add default constraint.

Add Column To Existing Table With Default Values and Column should accept Null as well. Also populate the Newly Added Column with Default Values at time of Adding Column

Alter Table dbo.Customer
ADD CountryName VARCHAR(50) 
Default 'USA'
WITH Values

Select * from  dbo.Customer

Fig 3: Add New column with Null constraint and default value and populate with Default values

As you can see in last sql script we used "With values" clause. If you use that, the existing records will be populated with default values. Also as we did not provide NOT Null Constraint, our table will accept Null values in case we want to save Null value in column.  If no value will be provided, default value will be inserted to the table.

--Check if Default is working fine
Insert into dbo.Customer(CustomerName,StreetAddress,City,State)
Select 'John Smith','Test Street Address','Charlotte','NC'

--Insert Null CountryName
Insert into dbo.Customer(CustomerName,StreetAddress,City,State,CountryName)
Select 'July River','Test Street Address','Jersey City','NJ',Null

Fig 4: Insert Records SQL Server Table after Adding New Column 

SQL Server Video Tutorial: Watch the Video for Above Scenarios How to Add Column To SQL Table and populate already existing rows with default value.