This article explains the SQL add column operation into an existing SQL table. We will also explore different examples of SQL add column operations.
Sometimes we want to add columns into an existing table. In existing tables, we might have records in it. We do not want to lose existing data as well. In many circumstances, we can drop the tables and recreate them but this is not recommended generally, especially in a production environment, as it can be destructive as it pertains to data. We can still perform a SQL add column operation using Alter Table command, which avoids have to drop tables, delete data, even if only temporarily.
Syntax
We can perform a SQL add column operation on a table with the following transact SQL command.
|
1 2 |
ALTER TABLE table_name ADD column_name column_definition; |
Prepare the environment
We need to select a Database table and insert data into it.
Execute the following query to create an Employee table in SQLShackDemo database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
USE [SQLShackDemo] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Employee]( [EmpID] [int] IDENTITY(1,1) NOT NULL, [EmpName] [varchar](50) NULL, [City] [varchar](30) NULL, [Designation] [varchar](30) NULL, PRIMARY KEY CLUSTERED ( [EmpID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO |
Execute the following query to insert sample data into it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 |
USE [SQLShackDemo]; GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (1, N'Charlotte Robinson', N'Chicago', N'Consultant' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (2, N'Madison Phillips', N'Dallas', N'Senior Analyst' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (3, N'Emma Hernandez', N'Phoenix', N'Senior Analyst' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (4, N'Samantha Sanchez', N'San Diego', N'Principal Conultant' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (5, N'Sadie Ward', N'San Antonio', N'Consultant' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (6, N'Savannah Perez', N'New York', N'Principal Conultant' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (7, N'Victoria Gray', N'Los Angeles', N'Assistant' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (8, N'Alyssa Lewis', N'Houston', N'Consultant' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (9, N'Anna Lee', N'San Jose', N'Principal Conultant' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (10, N'Riley Hall', N'Philadelphia', N'Senior Analyst' ); GO SET IDENTITY_INSERT [dbo].[Employee] OFF; GO |
In the following screenshot, we can see the existing data in the Employee table.
SQL add column operation on an existing SQL table
We want to add the column department in the Employee table. Suppose we have many columns in a table; we need to check if a particular column exists in the SQL table or not. If the specified column does not exist, we want to create it with the appropriate data type.
We can use the INFORMATION_SCHEMA view to check tables and their columns within a database. Execute the following code to get a list of columns, their data type in Employee table.
|
1 2 3 4 5 6 7 8 |
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Employee'; |
In this output, we can see the Employee table contains 4 columns.
Let’s add a new column Department with following Alter Table command.
|
1 2 |
ALTER TABLE Employee ADD Department Varchar(50) |
Execute this query and select records from the Employee table. In the following screenshot, we can look at the new column Department. All existing records contain a NULL value in this column.
Previously, we checked all columns in the Employee table using INFORMATION_SCHEMA view. In the following query, we want to create a Department table only if it does not exist in the Employee table.
|
1 2 3 4 5 6 7 8 9 10 11 |
IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Employee' AND COLUMN_NAME = 'Department' ) BEGIN ALTER TABLE Employee ADD Department VARCHAR(50); END; |
We can add a column in an existing table if it allows NULL values or have a default value defined on it. We can try to add Not NULL column in the existing SQL table, but it gives the following error message,
|
1 2 3 4 5 6 7 8 9 10 11 |
IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Employee' AND COLUMN_NAME = 'Phone' ) BEGIN ALTER TABLE Employee ADD Phone VARCHAR(15) Not NULL; END; |
SQL add column operation to an existing SQL table with a default value
Suppose we want to add the column IsActive column into the Employee table. We can have the following values in this column
- Value 1: Employee is active
- Value 0: Employee is not active
By default, all existing and new employee should have Value 1 in IsActive column. We can specify a value using default constraint.
If we try to add a column with a Not NULL value in the existing SQL table, we get following error message,
|
1 2 3 4 5 6 7 8 9 10 11 |
IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Employee' AND COLUMN_NAME = 'Phone' ) BEGIN ALTER TABLE Employee ADD IsActive bit DEFAULT(1); END; |
Execute this query and Select records from a table. For existing records, it does not update the default values.
If we insert any new record in this table, it gets default value as per the following screenshot.
SQL add column operation to an existing SQL table with an identity column
In SQL Server, we use the Identity function to define a default and auto increment value for each new row. We can add an identity column to the existing SQL table as well. Let’s create a new table Employee_new without an identity column.
|
1 2 3 4 5 6 |
CREATE TABLE [dbo].[Employee_new]( [EmpID] [int] NOT NULL, [EmpName] [varchar](50) NULL, [City] [varchar](30) NULL, [Designation] [varchar](30) NULL ) |
Once the table is there, we can add an identity column with the following query.
|
1 2 3 4 5 6 7 8 9 10 11 |
IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Employee_new' AND COLUMN_NAME = 'ID' ) BEGIN ALTER TABLE Employee_new ADD ID INT IDENTITY(1,1) NOT NULL END; |
We created the Identity column in a table without any record in it. Let’s drop the table and recreate it. Insert a few records with the following query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
INSERT INTO [dbo].[Employee_new] ([EmpID], [EmpName], [City], [Designation] ) VALUES (8, N'Alyssa Lewis', N'Houston', N'Consultant' ); GO INSERT INTO [dbo].[Employee_new] ([EmpID], [EmpName], [City], [Designation] ) VALUES (9, N'Anna Lee', N'San Jose', N'Principal Conultant' ); |
We have data in the Employee_new table. Let’s add an Identity column with Alter table command.
|
1 2 3 4 5 6 7 8 9 10 11 |
IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Employee_new' AND COLUMN_NAME = 'ID' ) BEGIN ALTER TABLE Employee_new ADD ID INT IDENTITY(1,1) NOT NULL END; |
In the following screenshot, we can see it updates existing records as well.
Multiple SQL add column operations for an existing SQL table with an identity column
We might need to add multiple columns to an existing SQL table. We can do it within the same Alter table command.
In the following query, we added two columns ZipCode and StateCode in a single Alter Table command. We need to specify all columns to add in a similar format.
|
1 2 3 4 |
ALTER TABLE Employee_new ADD ZipCode INT NULL, StateCode INT NULL; GO |
We can get details of all columns and their properties using sp_help command.
|
1 |
sp_help 'Employee_new' |
SQL add column operation to an existing SQL table with the table designer in SSMS
In previous examples, we used t-SQL to add columns in the existing table. We might not be familiar with writing t-SQL code. We can use the SSMS GUI as well to add a column.
Right click on the table and click on Design.
It opens a table designer. We can see all existing column, their data types, default values and other properties for a specified table in a table designer
Provide a column name and select data types from the drop-down. We can add multiple columns in this with appropriate data types.
Once done, Save and exit the table designer in SSMS. If you try to close it without saving changes, we get a warning message as well.
Click on Yes to save new column in the existing table. We can either run a Select statement to verify the new column or use sp_help command to list all columns and their properties.
|
1 |
sp_help 'Employee.' |
In the following screenshot, we can see a new column in the Employee table.
Conclusion
In this article, we explored SQL add column operations to add a a new column to an existing SQL table. We can use both the GUI and transact SQL method to do it. I hope you found this article helpful. You can provide feedback or comments in the comments section below.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023