SQL Server ALTER TABLE ADD Column

The ALTER TABLE ADD is a Data Definition Language (DDL) command that is used to alter the structure of the table by adding an extra column based on the new requirement. Using ALTER TABLE ADD we can also add new constraints and also indexes for the table. With the help of this command, We can simply apply modifications to the columns of our tables.

Syntax:

ALTER TABLE table_name
ADD column1 data_type , ADD column2 data_type

Explanation: In the above query, We have added two columns called column1 and column2 in table table_name. We follow this syntax for adding columns to our table. We will understand with the help of examples.

Usage of ALTER TABLE ADD Column

Step 1: Create database Geeksforgeeks by using the following SQL query

CREATE DATABASE Geeksforgeeks

Step 2: Use the GFG Database.

USE Geeksforgeeks

Step 3: Create a table with the name studentsMark which contains the studentName along with their details

CREATE TABLE studentsMarks
(
studentId INT PRIMARY KEY,
studentName VARCHAR(100),
courseId VARCHAR(100),
marksObtained INT,
);

Step 4 : Inserting Sample Data into the table

INSERT INTO studentsMarks
VALUES (19104060, 'Student1', 'ECPC-1001', 25),
(19104061, 'Student2', 'ECPC-1001',96),
(19104062, 'Student3', 'ECPC-1001',81),
(19104063, 'Student4', 'ECPC-1001',85),
(19104064, 'Student5', 'ECPC-1001', 86),
(19104065, 'Student6', 'ECPC-1001', 55),
(19104066, 'Student7', 'ECPC-1001',70),
(19104067, 'Student8', 'ECPC-1001',71),
(19104068, 'Student9', 'ECPC-1001',65),
(19104069, 'Student10', 'ECPC-1001',68);

Step 5 : Check the table

SELECT * FROM studentsMarks

The Result Looks Like:

StudentMark Table

Adding a Single Column

Let’s add the new column name gradeObtained into our existing table called studentsMarks, the values of gradeObtained column are initially NULL.

Query

ALTER TABLE studentsMarks
ADD gradeObtained VARCHAR(50)
SELECT * FROM studentsMarks

The Result Looks Like:

Table After Adding Single Column

Explanation: This query adds the gradeObtained column in the table

Adding a Multiple Column

Let’s see how we add multiple columns with constraints into our table.

Query

ALTER TABLE studentsMarks
ADD gradeLockStatus VARCHAR(50),
backLogs INT CONSTRAINT backLogConstraint DEFAULT 0 NOT NULL,
remarks VARCHAR(50) CONSTRAINT remarkConstraint DEFAULT 'NA' NOT NULL
SELECT * FROM studentsMarks

The Result Looks Like:

Table After Adding Multiple Column

Explanation: After executing this query, the studentsMarks table will have the additional columns gradeLockStatus, backLogs, and remarks with the specified data types, default values, and constraints.

Conclusion

The ALTER TABLE ADD command plays a key role when we want to make any column additions for the existing Table as the requirements come on updating the applications. Sometimes it might happen that at the time of table creation, we forget to add some columns to our table. We resolve this problem by using ALTER TABLE ADD command, which allows us to add columns to our table.