`IDENTITY_INSERT` is a Sql Server database option which permits values for an identity column on a table to be explicitly specified during an Insert operation.
When using the Identity Property on a column in a Sql database, values for the column are then assigned by Sql Server, and the assigned value can be returned using SCOPE_IDENTITY() or similar.
However, in certain conditions, it may be a requirement to suppress the automatic behavior of the identity property on the column, and instead provide a user-specified value for the column.
The SET IDENTITY_INSERT option can be used to achieve this.
Example:
create table MyTable
(
id int identity(1,1) NOT NULL,
name nvarchar(50) NOT NULL
);
Under normal conditions, with IDENTITY_INSERT off, values for the identity column cannot be manually specified:
SET IDENTITY_INSERT MyTable OFF; -- Which is the Default
insert into MyTable(name) values ('Andrew');
-- 1 row(s) affected
insert into MyTable(id, name) values (5, 'Andrew');
-- Cannot insert explicit value for identity column in table 'MyTable' when IDENTITY_INSERT is set to OFF.
By enabling IDENTITY_INSERT, values for id MUST be provided
SET IDENTITY_INSERT MyTable ON;
insert into MyTable(id, name) values (6, 'Charles');
-- 1 row(s) affected
insert into MyTable(name) values ('Dan');
-- Explicit value must be specified for identity column in table 'MyTable' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
References
- MSDN Identity(Property) http://msdn.microsoft.com/en-us/library/ms186775.aspx
