SQL Query to Add a New Column After an Existing Column in SQL

Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, SQL Server, Postgres, etc. In Microsoft SQL Server, we can change the order of the columns and can add a new column by using ALTER command. ALTER TABLE is used to add, delete/drop or modify columns in the existing table. It is also used to add and drop various constraints on the existing table.

Step 1: Create a database

Let’s create a database employee.

Query:

CREATE DATABASE employee;

Step 2: Create a table

Now create a table employee_info.

Query:

CREATE TABLE employee_info
( Employee_id INT,
First_Name VARCHAR(25),
Last_Name VARCHAR(25),
Salary INT,
City VARCHAR(20)); 

INSERT INTO employee_info
VALUES (1,'Monika','Singh',800000,'Nashik'),
(2,'Rahul','Kumar',450000,'Mumbai'),
(3,'Sushant','Kumar',500000,'Pune'),
(4,'Ajay','Mehta',600000,'Mumbai');

Step 3: To view a database schema we use the following query.

Query:

EXEC sp_help 'dbo.employee_info';

Output:

Now, let’s add a new column Gender in the table. Then we use ALTER table command. 

Step 4: Alter table.

ALTER TABLE employee_info ADD Gender CHAR(1) CHECK (Gender IN ('M','F'));

Output:

Now, the new column gets added after City i.e. at the last successfully.

Take another case using a query, If we want the Gender column after Last_Name, then we can write the query as shown below.

Query:

SELECT Employee_Id,First_Name,Last_Name,Gender,Salary,City FROM employee_info;

Output:

My Personal Notes arrow_drop_up

Save