Questions tagged [identity-insert]

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

1. MSDN Identity(Property) http://msdn.microsoft.com/en-us/library/ms186775.aspx