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