How to add an IDENTITY to an existing column in SQL?

There is no straightforward way to add IDENTITY to an existing column. We need to follow a series of steps to achieve this. There are two ways to do this.

1. Creating New Table
2. Creating New Column

We need to convert the ‘student_id’ column of the table below to an IDENTITY column.

CREATE TABLE student (
student_id INT,
student_name VARCHAR(50)
);
GO

INSERT INTO student(student_id, student_name)
VALUES (100,'Jack');
INSERT INTO student(student_id, student_name)
VALUES (101,'Rose');

-- Output
student_id student_name
----------- --------------
100 Jack
101 Rose

Creating New Table

We need to follow the below steps in this approach.

/*
Step1: Create a new table with exactly the same columns and constraints as its original table. Add IDENTITY in the 'student_id' column.
*/

CREATE TABLE student_new (
student_id INT IDENTITY(1, 1),
student_name VARCHAR(50)
);
GO

/*
Step2: Insert all data from the old table to the new table. We need to set IDENTITY_INSERT ON since we are adding data manually in the IDENTITY Column.
*/

SET IDENTITY_INSERT dbo.student_new ON
GO

IF EXISTS (SELECT 1 FROM dbo.student)
INSERT INTO dbo.student_new(student_id, student_name)
SELECT student_id, student_name
FROM dbo.student
GO

SET IDENTITY_INSERT dbo.student_new OFF
GO

/*
Step3: Drop old table. We must be very careful that we have saved all old data to the new table before deleting the old table.
*/
DROP TABLE dbo.student
GO
/*
Step4: Rename the new table name with the old table name
*/
Exec sp_rename 'student_new', 'student'

INSERT INTO student( student_name)
VALUES ('John');

SELECT student_id, student_name
FROM student

--Output
/*
student_id student_name
----------- --------------------------------------------------
100 Jack
101 Rose
102 John
*/

In this approach, the ‘student_id’ column’s value is the same as the value in the old table.

Creating New Column

We need to follow the below steps in this approach.

/*
Step1: Add a new column with IDENTITY in the table
*/
ALTER TABLE student
ADD student_id_new INT IDENTITY(1, 1)
GO

/*
Step2: Drop the old column from the table
*/

ALTER TABLE student DROP COLUMN student_id
GO

/*
Step2: Rename new column name with old column name
*/

EXEC SP_RENAME 'student.student_id_new', 'student_id','Column'
GO

SELECT student_id, student_name FROM student

-- Output
/*
student_id student_name
----------------------------
1 Jack
2 Rose
*/

Up Next:

Read How to initialize an AUTO_INCREMENT column in MySQL