This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Last week, I was exploring few SQL Server forums and found an interesting discussion on the IDENTITY Column of SQL Server.
One of the discussions like How to set IDENTITY to existing column of a Table?
Actually, there is no any specific option for setting IDENTITY to existing Column of a Table.
But there are different tricks to do this, sharing one of the tricks here…
Create a sample table without identity column:
|
1 2 3 4 5 |
CREATE TABLE tbl_TestIdentity (ID INT, Name VARCHAR(10)) INSERT INTO tbl_TestIdentity VALUES (1,'ABC'), (2,'XYZ'),(3,'PQR') ,(4,'WER'),(5,'JKL'),(6,'ZXC') |
Check the data:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT *from tbl_TestIdentity ID Name ----------- ---------- 1 ABC 2 XYZ 3 PQR 4 WER 5 JKL 6 ZXC |
Add new IDENTITY Column to table:
|
1 |
ALTER TABLE tbl_TestIdentity ADD ID2 INT IDENTITY (1,1); |
Drop old ID Column:
|
1 |
ALTER TABLE tbl_TestIdentity DROP COLUMN ID |
Rename new column to old column name:
|
1 |
Exec sp_rename 'tbl_TestIdentity.ID2', 'ID', 'Column' |
Result:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT *from tbl_TestIdentity Name ID ---------- ----------- ABC 1 XYZ 2 PQR 3 WER 4 JKL 5 ZXC 6 |
Leave a Reply