How to update values in identity column in SQL Server?

In a rare scenario, I had to update the values in the identity column. There is no straightforward way to update identity values. Here is a workaround I have followed to get the work done.

Note

Before going through the workaround to update the values in identity column, you have to understand that:

• You cannot update the value of the identity column in SQL Server using UPDATE statement.
• You can delete the existing column and re-insert it with a new identity value.
• The only way to remove the identity property for the column is by removing the identity column itself.

Steps for updating existing identity column values

1. Remove all the foreign key constraints referencing the identity column.
2. Copy all the records from the identity table and insert it to a staging table.
3. Now, switch ON IDENTITY_INSERT for the identity table to allow inserting values to the identity Column.
4. Delete the records from the identity table.
5. Insert the records from the staging table with your preferred identity values.
6. Now switch OFF IDENTITY_INSERT.
7. Finally delete the staging table.

Syntax T-SQL code for the above steps.

/* Step 1: Remove all the foreign key constraints 
	referencing the identity column. */

/* Step 2: Insert the records to a staging table. */
SELECT *  INTO tmp_identityTable FROM identityTable;

/* Step 3: Allow insertion in identity column. */
SET IDENTITY_INSERT identityTable ON;
GO

/* Step 4: Delete all the records from the identity table. */
DELETE FROM identityTable;

/* Step 5: Insert back all the records with the new identity value. */
INSERT INTO identityTable (IDCol, DateCol, OtherCols)
SELECT ID+1 as IDCol /* You can use any other identity generation logic here. */
, DateCol, OtherCols FROM tmp_identityTable
ORDER BY DateCol ASC;

/* Step 6: Switch off the identity insert. */
SET IDENTITY_INSERT identityTable OFF;
GO

/* Step 7: Drop the staging table. */
DROP TABLE tmp_identityTable;

Steps for changing the identity value of new records

In case if you do not want to change the identity values of the existing records, but you want to change the identity value of the new records to start from a higher number, you can use DBCC CHECKIDENT.

For example, if the identity value of the last existing record is 18, and you have to insert a new record to start from 1000, then use the below code.

/* Syntax */
DBCC CHECKIDENT(tableName, RESEED, newReseedValue)

/* Example */
DBCC CHECKIDENT(TestTable, RESEED, 1000)

Reference

• More about DBCC CHECKIDENT at Microsoft Docs.
• More about SET IDENTITY_INSERT at Microsoft Docs.