Alter column to be identity

Question

I've already read the following answers about the impossibility to alter a column into identity once has been created as a regular int.

Adding an identity to an existing column

How to Alter a table for Identity Specification is identity SQL Server

How to alter column to identity(1,1)

But the thing is I have a table which has been migrated to a new one where the ID was not declared as identity from the beginning, because the old table which was created with an ID identity a long time ago has missing rows due to a purge of historical data. So as far as I know, if I add a new column as identity on my new table, it will automatically create the column sequentially and I need to preserve the IDs from the old table as-is because there is already data linked to these previous IDs.

How can I do transform my ID column from the new table as identity but not sequentially, but with the IDs from the old table?

Answer 1

You could try this approach:

1. Insert rows with old ID with SET IDENTITY_INSERT <new table> ON. This allows you to insert your own ID.
2. Reseed the Identity, setting it to the highest ID value +1 with DBCC CHECKIDENT ('<new table>', RESEED, <max ID + 1>). This will allow your Identity to increase from the highest ID and forward.

Something like this in code:

-- Disable auto increment
SET IDENTITY_INSERT <new table> ON
-- <INSERT STUFF HERE>
SET IDENTITY_INSERT <new table> OFF

-- Reseed Identity from max ID
DECLARE @maxval Int
SET @maxval = ISNULL(
(
    SELECT
        MAX(<identity column>) + 1
    FROM <new table>
), 0)
DBCC CHECKIDENT ('<new table>', RESEED, @maxval)

EDIT: This approach requires your ID-column to be an Identity, of course.