What is the way to add an identity to an existing column?

Question

I want to change the primary key of a table to an identity column, and there are a number of rows available in the table already.

I want to clean up script IDs to ensure they're sequential starting at 1, it worked fine for me. 

I want to ask with which SQL command  I can alter the column to have an identity property?

Answer 1

There is no way to alter the existing columns for identity.

You can try these two options:

Create a new table with identity & drop the existing table

Create a new column with identity & drop the existing column

• To create a new table you can retain the existing data values on the newly created identity column. You may lose all data if 'if it does not exist' is not satisfied, therefore ensure that you put the condition on the drop also. 

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'

• To create a new Column: You won’t be able to retain the existing data values on the newly created identity column, The identity column will hold the sequence of numbers.

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'

If you want to get more insights into SQL, check out this SQL Course from Intellipaat.