Adding an identity to an existing column

[ForumBot]

Adding an identity to an existing column

[ForumBot]

You can't alter the existing columns for identity.

You have 2 options,

-
Create a new table with identity & drop the existing table

-
Create a new column with identity & drop the existing column

Approach 1. (*New table*) Here you can retain the existing data values on the newly created identity column. Note that you will lose all data if 'if not exists' is not satisfied, so make sure you put the condition on the drop as well!

`CREATE TABLE dbo.Tmp_Names
(
Id int NOT NULL
IDENTITY(1, 1),
Name varchar(50) NULL
)
ON [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT *
FROM dbo.Names )
INSERT INTO dbo.Tmp_Names ( Id, Name )
SELECT Id,
Name
FROM dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

```

Approach 2 (*New column*) You can’t retain the existing data values on the newly created identity column, The identity column will hold the sequence of number.

`Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

```

See the following Microsoft SQL Server Forum post for more details:

[How to alter column to identity(1,1)](https://learn.microsoft.com/en-us/answers/questions/1691006/how-to-alter-column-to-identity(1-1))