Add Identity property to the existing column in a Table which has records

In this blog let us see how to add identity property for the existing column in a table which already has rows.

--My Current table

CREATE TABLE dbo.tbl_Test
 (
Id int NOT NULL,
  UserName varchar(50) NULL,
Password varchar(50) NULL
 ) ON [PRIMARY]
 go

-- Creating new table with identity column
CREATE TABLE dbo.tbl_Temp
 (
Id int NOT NULL IDENTITY(1, 1),
 UserName varchar(50) NULL,
Password varchar(50) NULL
 ) ON [PRIMARY]
 go

--Turn off identity and copy records from current table
 SET IDENTITY_INSERT dbo.tbl_Temp ON
go
IF EXISTS ( SELECT * FROM dbo.tbl_Test )
 INSERT INTO dbo.tbl_Temp ( Id, UserName,Password ) SELECT Id, UserName,password FROM dbo.tbl_test
go

-- Turn on the identity
SET IDENTITY_INSERT dbo.tbl_Temp OFF
go
-- Dropping the old table
DROP TABLE dbo.tbl_Test
go
--rename the new table with old table name
 Exec sp_rename 'tbl_Temp ', 'tbl_Test'