How to add an IDENTITY to an existing column in SQL?
- How-Tos FAQs
- February 1, 2019
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.
- Creating New Table
- 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 RoseCreating 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
*/
