Posted By: Anonymous
I need to change the primary key of a table to an identity column, and there’s already a number of rows in table.
I’ve got a script to clean up the IDs to ensure they’re sequential starting at 1, works fine on my test database.
What’s the SQL command to alter the column to have an identity property?
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: