Pages

Thursday 4 July 2013

How To Add Identity Property to Existing Column in Table in SQL Server

How To Add Identity Property to Existing Column in Table in SQL Server

Create Table As Below

CREATE TABLE UserDtls

(

UserId int PRIMARY KEY,

UserName varchar(120),

Qualification varchar(50)

)

Now, Insert Data As Below

INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(1,'sizar','Be')

INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(2,'sahil','Be')

INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(3,'salim','Be')

The Table Is As Follow 

How To Add Identity Property to Existing Column in Table in SQL Server
How To Add Identity Property to Existing Column in Table 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.

No comments:

Post a Comment