IDENTITY_INSERT
IDENTITY_INSERT is a set statement in SQL Server that allows explicit values to be inserted into the identity column of a table. Typically, the identity column auto-increases its value for each new record. But, by setting IDENTITY_INSERT to ON for a specific table, it's able to programmatically assign a value to the identity column. It's recommended to be used sparingly due to potential conflicts with auto-generated values. Once utilized, it must be set back to OFF to re-enable automatic identity value generation.
Example
SET IDENTITY_INSERT dbo.YourTable ON;
INSERT INTO dbo.YourTable (ID, Name)VALUES (5, 'Sam');
SET IDENTITY_INSERT dbo.YourTable OFF;Output
New entry in YourTable:
| ID | Name |
|---|---|
| 5 | Sam |
Explanation
In the example provided, the IDENTITY_INSERT property was enabled for YourTable, allowing an explicit value (in this case, ‘5’) to be inserted into the identity column ‘ID’. The name column received the value ‘Sam’. After inserting the data, the IDENTITY_INSERT property was turned off to ensure the SQL Server manages the identity column automatically again.