CODEWITHSUNDEEP
sqlsql-servert-sql
user6210073
user6210073

Reputation: 23

Insert less than max value in identity column in SQL server

I deleted some of the records from a table and want to reinsert them from a backup table. The primary key column is identity column and it have larger values than the deleted ones.When I try to insert its throwing error. I cannot reseed the column. Please let me know if it possible in SQL server.

Upvotes: 2

Views: 866

Answers (1)

Igor
Igor

Reputation: 62213

Before you do this make sure that there are no other processes that inserting into this table or they could receive an error when the IDENTITY is disable. Alternatively wrap the whole thing in transaction with the isolation level set to SERIALIZABLE to prevent any concurrent changes coming from other processes.

  1. Disable the IDENTITY on your table/column.
  2. Execute your INSERT statements from your backup, be sure you specify the value of the column that was your IDENTITY column.
  3. Re-enable the IDENTITY on your table/column.

Sql:

SET IDENTITY_INSERT <your_table> OFF
-- Exceute Inserts
SET IDENTITY_INSERT <your_table> ON

Notes

You should probably execute your inserts wrapped in a transaction. That way you can roll everything back if there is a failure. You should also not have any conflicts as the identity always increments so re-adding deleted records should not pose a problem. You might want to check your index fragmentation though on the PK once you are done.

Upvotes: 2

Related Questions