How To Re-Insert a Row with Identity Value

SQL Tech

How to insert deleted identity row in SQL Server.

Naren 0Comment

To insert a deleted identity row back into a SQL Server table while preserving the specific identity value, you need to temporarily enable IDENTITY_INSERT for the table.

  • Enable IDENTITY_INSERT on the table.
  • Insert the row with the specific identity value.
  • Disable IDENTITY_INSERT on the table.

Assume you have a table named td_employee with an identity column EmployeeID and other columns like FirstName, LastName, and Email.

Note:

  • Ensure Unique Identity Values: Make sure the identity value you’re inserting does not already exist in the table, as this would cause a primary key violation.
  • Performance Impact: Avoid keeping IDENTITY_INSERT on for extended periods, as it can affect performance and concurrency.
  • Permissions: You need appropriate permissions to enable/disable IDENTITY_INSERT and perform inserts.

Explanation:

  • SET IDENTITY_INSERT [TableName] ON: This command allows you to explicitly insert values into the identity column of the table.
  • INSERT INTO [TableName] ([ColumnList]) VALUES ([Values]): This is the standard SQL insert statement, but with the identity column explicitly included in the column list.
  • SET IDENTITY_INSERT [TableName] OFF: This command disables the ability to insert explicit values into the identity column, returning control of the identity column to SQL Server.

By following these steps, you can reinsert a deleted row with its original identity value, ensuring data integrity and continuity in your table.