Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server - Add Identity Property to Existing Column in Table

Mar 7, 2013
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 RSS subscribe by email Subscribe by Email

1 comments :

Anonymous said...

You are a lifesaver. Thank you!

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.