Insert explicit values into an IDENTITY column
Use SET IDENTITY_INSERT to write a value into an identity column, then turn the option off again.
An IDENTITY column normally receives its values from SQL Server. To write an explicit value into it, turn on SET IDENTITY_INSERT for the table, insert the row with an explicit column list, then turn it off again.
SET IDENTITY_INSERT MyTable ON;
INSERT INTO MyTable
(MyIdentityField, MyFirstField, MySecondField)
VALUES
(12345, 'ABCDE', 'etc...');
SET IDENTITY_INSERT MyTable OFF;
Notes
- The column list in the
INSERTnames the identity column explicitly, which is required when a value is supplied for it. SET IDENTITY_INSERTcan be ON for one table at a time in a session. Turn it off as soon as the insert is done.