Thursday 20 August 2015

SQL Server - Disable Identity Insert temporarily and enable back

At times, you might want to Disable SQL Server Identity Insert in order to insert some records with ids our of series and after the inserts are done you want to enable back the identity insert.
Find the code snippet below which lets you do it for SQL Server:
SET IDENTITY_INSERT <TableName> ON

-- Insert into the table <TableName> with the ids you prefer.

SET IDENTITY_INSERT <TableName> OFF

SET IDENTITY_INSERT ON allows explicit values to be inserted into the identity column of a table.