SQL Server - Add Identity Property to Existing Column in Table

Here I will explain how to add identity property to existing column in table using SQL Server 2008 or add identity column to existing table SQL Server.

To add identity property to existing column you need to follow below steps for that first create one sample table like as shown below

CREATE TABLE UserDtls

 (

 UserId int PRIMARY KEY,

 UserName varchar(120),

 Qualification varchar(50)

 )

Once we create UserDtls table insert data like as shown below

INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(1,'Nayab',MCA')

INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(2,'Mahesh','MSC')

INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(3,'Mani','CA')

Once we insert data our table will be like as shown below

In above table I want to make UserId Column as Identity column. To add identity property for existing column you need to follow this article Create Identity Column or Auto increment column in SQL Server .

We can create identity column with above method only whenever we don't have any data in table otherwise we need to use T-SQL query for that follow below steps

     1.    Create another table(temp1) with same structure as old table(UserDtls) table with identity column.

     2.    Now move the data from UserDtls table to temp1 table for that you need to ON Identity insert property to know more about it check this article insert values in identity column in SQL

     3.    Once inserted drop original table UserDtls and rename temp1 to UserDtls.

For above steps below is the code we need to run to create identity column for existing table

---- Create New Table with Identity Column ------

 CREATE TABLE temp1

 (

 UserId INT PRIMARY KEY IDENTITY,

 UserName VARCHAR(120),

 Qualification VARCHAR(50)

 )

----Insert Data into newly created table----------

SET IDENTITY_INSERT temp1 ON

 IF EXISTS(SELECT TOP 1 * FROM UserDtls)

 BEGIN

 INSERT INTO temp1(UserId,UserName,Qualification)

 SELECT UserId,UserName,Qualification FROM UserDtls

 END

SET IDENTITY_INSERT temp1 OFF

--------Once Data moved to new table drop old table --------

 DROP TABLE UserDtls

 -------Finally rename new table name to old table name

 EXEC sp_rename 'temp1','UserDtls'

By using above method we can add identity property to existing column in table using using SQL Server.