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
|
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.
