How To Add Identity Column To Existing Table In SQL Server

I was working with my team on a requirement from one of my clients. We were required to add an identity column to an existing critical table in our SQL database. As part of the article, I will explain our approaches to handling this, as it is not supported directly.

Note that you can’t directly add identity to an existing column in your existing table. This approach is not supported, so you need to follow a specific approach to handle this situation. Here’s a step-by-step guide on how to add an identity column in an SQL server for an existing table.

Now, let me help you understand with an example.

Suppose we have an existing table, Customer, without any identity column.

CREATE TABLE Customer 
(
  CustomerID int NOT NULL,
  Customer_name varchar(20),
  Department varchar(30)
);

Now, if I try executing the below script to add the IDENTITY property to the CutomerID column, It won’t allow me, and through error, Let’s check out that.

Check out: How to add identity column to existing table in SQL Server

ALTER TABLE Customer
ALTER COLUMN CustomerID int IDENTITY(1,1);

After executing the above SQL query, I got the error below. Check out the screenshot below.

Now, below are the approaches you can follow to achieve this functionality.

Approach 1: Drop the old Column and Recreate it with the IDENTITY property.

Execute the below script to drop or delete the old column CustomerID with no IDENTITY property in the Customer table. Check out the screenshot below for your reference.

ALTER TABLE Customer 
DROP COLUMN CustomerID;

Let’s use the script below to create a new column with the same name, CustomerID, with IDENTITY property.

ALTER TABLE Customer
ADD CustomerID int IDENTITY(1, 1) NOT NULL;

To cross-check whether the new column is IDENTITY or not, right-click on the column and select Properties. The Identity value is True. Check out the screenshot below.

Check out: How to check identity column in SQL Server

Approach 2: Create a new table with the IDENTITY column and Drop the Old table.

Execute the below SQL query to create a new table Customer_New with CustomerID as the IDENTITY column. Check out the screenshot below.

CREATE TABLE Customer_New
(
  CustomerID INT IDENTITY(1, 1),
  Customer_name varchar(20),
  Department varchar(30)
);

Now, you can copy the data from the old table to the new table using the below insert query.

INSERT INTO Customer_New (CustomerID, Customer_name, Department)
SELECT CustomerID, Customer_name, Department
FROM Customer;

After executing the above query, we got the error “Cannot insert explicit value for identity column in table ‘Customer_New’ when IDENTITY_INSERT is set to OFF.” as shown below.

This error occurs because you are not allowed to insert an explicit value into an autogenerated IDENTITY column. To fix this issue, you can execute the query below.

SET IDENTITY_INSERT Customer_New ON

Now, you can again execute the above insert query to move the data from the old table to the new one. After the data has been successfully moved, you can execute the below command to set the IDENTITY_INSERT Off.

SET IDENTITY_INSERT Customer_New OFF

Now, drop the old table using the below SQL query.

DROP TABLE Customer;

Now, you can execute the query below to rename the new table to the old one, as shown in the screenshot below.

EXEC sp_rename 'Customer_New', 'Customer';

Now, you can execute the query below to Update the IDENTITY seed, as shown in the screenshot below, and you are done.

DBCC CHECKIDENT('Customer');

Conclusion

Setting up an Identity column in an existing column is not supported directly. Still, to achieve this, you need to recreate the column or the whole table and later remove the old column or table.

You may also like following the articles below

• How to create a table with an identity column in SQL Server
• How to reset identity column value in SQL Server