SQL ADD COLUMN – Add Column To Database Table

Last modified date:

We can add a column to the table in two ways. First is using sql server management studio graphical interface. Second one is using SQL command ADD COLUMN.

T-SQL ADD COLUMN Syntax:

ALTER TABLE dbo.Table
ADD <Column> <Data Type> NULL (or) NOT NULL

Column will be created on the end of the table. If the table into which we want to add new column is not empty (with 1 or more rows), we need to create column with NULL values property. The operation will in opposite case result in an error.

Example of Column Addition Using ADD COLUMN Statement

1) We create table with employees dbo.Employee containing 3 fields

  • ID – column with primary key starting from 1 and ascending with every added record by 1: IDENTITY(1,1). More follows.
  • name of employee
  • surname of employee

2) We add 2 records into the table – employee Daenerys Targaryen and Yennefer from Vengerberg

3) We add column “Salary”

Solution

1) Create table

CREATE TABLE dbo.Employee (
   [ID] INT IDENTITY(1,1) PRIMARY KEY,
   [Name] VARCHAR(255) NOT NULL,
   [Surname] VARCHAR(255) NOT NULL
);

2) Inserting data into the table

INSERT INTO dbo.Employee ([Name], [Surname])
VALUES ('Daenerys','Targaryen'),
('Yennefer', 'Vengerberg');

ADD COLUMN Example - inserting data into the table

3) Inserting new column “Salary”

ALTER TABLE dbo.Employee
ADD [Salary] INT NULL;

After adding of the new column are values of column “Salary” NULL. It is, therefore, suitable to initiate table UPDATE

Note: If we try to add a column with NOT NULL property, the result will be an error

ALTER TABLE dbo.Employee
ADD [Salary] INT NOT NULL;

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column ‘Salary’ cannot be added to non-empty table ‘Employee’ because it does not satisfy these conditions.

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: SQL Tutorials Tags:

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.