Add a Column to Existing Table in SQL Server

Here is the syntax to add a column to an existing table in SQL Server:

ALTER TABLE [database_name].[table_schema].[table_name]
ADD new_column_name data_type

In this short guide, you’ll see two approaches to add a column:

  1. Using the ALTER TABLE query
  2. Using the SQL Server Management Studio interface

Approach 1: Using the ALTER TABLE query to Add a Column

Suppose that you have a database called TestDB. This database contains a table called People (where dbo is the table schema).

The ‘People’ table currently has the following columns and data:

Add a Column to Existing Table in SQL Server

The ultimate goal is to add an additional column called ‘City‘ with the following values (as highlighted in red):

How to Add a Column to Existing Table in SQL Server

To add the ‘City’ column, you can use the ALTER TABLE query:

ALTER TABLE [database_name].[table_schema].[table_name]
ADD new_column_name data_type

For our example:

  • The database_name is: TestDB
  • The table_schema is: dbo
  • The table_name is: People
  • The new_column_name is: City
  • The data_type is: nvarchar(50)

So the complete ALTER TABLE query for our example would look as follows:

ALTER TABLE [TestDB].[dbo].[People]
ADD City nvarchar(50)

Once you run the above query, the new column will be added:

Completed

Let’s run the following SELECT query to see the results:

SELECT * FROM TestDB.dbo.People

Notice that the ‘City’ column was added at the end of the table:

Add a Column to Existing Table in SQL Server

Also note that each value under the City column is currently NULL.

You can then use an UPDATE query to replace those NULL values with actual city names:

UPDATE [TestDB].[dbo].[People]
SET City = 
	CASE WHEN ID = 1 THEN 'London'
	     WHEN ID = 2 THEN 'New York'
	     WHEN ID = 3 THEN 'Sydney'
	     WHEN ID = 4 THEN 'Geneva'
	     WHEN ID = 5 THEN 'Paris'
	END

Run the above UPDATE query.

Then, rerun the following SELECT query to see the new values:

SELECT * FROM TestDB.dbo.People

You’ll now see the actual city names:

Add a Column to Existing Table in SQL Server

Approach 2: Using the Interface to Add a Column to a Table in SQL Server

Optionally, you can add a column to an existing table using the SQL Server Management Studio interface.

Let’s now add a new column called ‘Country‘ to the existing ‘People’ table.

First, locate the ‘People’ table under the Object Explorer:

Object Explorer

Click on the ‘+‘ sign:

Plus sign

Right-click on the ‘Columns‘ folder:

Right-Click Folder

Click on ‘New Column…‘ as follows:

New Column

Add the Column Name and Data Type, and then Save the table:

Data Type

Finally, run the SELECT query below to confirm that the new column was added:

SELECT * FROM TestDB.dbo.People

As before, the new Country column would contain NULL values:

Add a Column to Existing Table in SQL Server

Let’s use an UPDATE query to replace those NULL values with actual country names:

UPDATE [TestDB].[dbo].[People]
SET Country = 
	CASE WHEN ID = 1 THEN 'England'
	     WHEN ID = 2 THEN 'United States'
	     WHEN ID = 3 THEN 'Australia'
	     WHEN ID = 4 THEN 'Switzerland'
	     WHEN ID = 5 THEN 'France'
	END

Finally, run the SELECT query below:

SELECT * FROM TestDB.dbo.People

As you can see, the new Country column was added and updated with the actual values:

Add a Column to Existing Table in SQL Server