SQL Server - Add Identity Property to Existing Column in Table

Introduction: 

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.

Description:
  
In previous articles I explained Create Identity Column or Auto increment column in SQL Server, Reset identity column in SQL Server, insert values in identity column in SQL Server, SQL Query to get month wise, year wise data and many articles relating to SQL Server, jQuery, JavaScript. Now I will explain how to add identity property to existing column in table using SQL Server 2008.

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,'Suresh','B.Tech') INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(2,'Rohini','MSC') INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(3,'Mahendra','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.

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email