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?

You can't update an identity column. You can however provide a value if you turn IDENTITY_INSERT on for that table. Essentially you will have to delete all the current rows and reinsert them with the identity values you want. — Sean Lange, Oct 07 '15 at 15:19
Conceptually, would it be acceptable to let the new identity come into existence as an X based auto-incrementing integer (where X = MAX([Your Old ID Column]) + 1), then update that identity field on rows where you want to keep the old identity to their old values? If so, I'll post steps on how to go about this as a formal answer - just want to make sure we're on the same page first. — LDMJoe, Oct 07 '15 at 15:20
@LDMJoe you can't update an identity column. If you know some way to update it I would be interested in seeing that. — Sean Lange, Oct 07 '15 at 16:15
@SeanLange That's why I asked "conceptually" - was going to do it in practice by populating a non-identity column, then turning on `IDENTITY_INSERT` and using those values set to what the OP desired to populate a new table. OP never answered, and somebody else has posted pretty much what I was gonna as an answer, and it has been accepted, so I think we're done with this. — LDMJoe, Oct 07 '15 at 20:35

tobypls · Accepted Answer · 2022-06-01T12:46:41.573

You could try this approach:

Insert rows with old ID with SET IDENTITY_INSERT <new table> ON. This allows you to insert your own ID.
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.

Warning: This approach doesn't work if you truncate the table. If you Truncate, then the identity is reset to the seed value when the table was first created...not the value you RESEED to. — Auction God, Aug 10 '22 at 20:03

score 0 · Answer 2 · answered Oct 07 '15 at 15:26

If you don't have nulls in the field that you want to copy over from your previous version, you could first figure out what the largest ID is by just doing a max(Id) select. Then using SSMS go add your new field and when you set it as identity, just set the SEED value to something higher than what your current max is so you don't have collisions on new inserts.

score 0 · Answer 3 · edited Sep 10 '19 at 03:15

I have a process where a temp table is used between a source file, CSV and the production table. The temp table has to match the CSV file columns, there is no PK in this data.

To find a set of rows before and after where the Azure Data Factory was failing, I imported over 2,000,000 rows into a temp table. The process stopped in Azure at 1,500,000 rows.

The error was that an integer or string would be truncated.

This line of code added a PK to the temp table and incremented it:

ALTER TABLE ##FLATFILETEMPBDI ADD ROWNUM INT IDENTITY

That would be the simplest solution to get a row number. I was then able to do this query to find the rows just before and after 1,500,000:

SELECT  
  ROWNUM
  , PARTDESCRIPTION
  , LEN(PARTDESCRIPTION) AS LENDESCR
  , QUANTITY
  , ONORDER
  , PRICE
  , MANUFACTURERPARTNUMBER
FROM ##FLATFILETEMPBDI 
WHERE ROWNUM BETWEEN 1499990 AND 1500005

Works perfectly -- was not planning on it to be that easy, was surprised as anyone to see that the ALTER TABLE with IDENTITY worked to do the numbering for me.

Alter column to be identity

3 Answers3