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');
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.