What is IDENTITY_INSERT?
The identity column is the numeric column in the SQL Server table whose value increments automatically. The value of the identity column is set by the SQL Server itself based on the definition provided by the user. Thus user can not explicitly set the value of such columns and they are unique. The identity column can not be NULL. IDENITY_INSERT is the session level property which when set to “ON” the user can explicitly set/insert the value for the identity column.
Syntax for IDENTITY column:
IDENTITY [( seed, increment)]
Here SEED is the start of the numeric value for the column data and it is incremented with the INCREMENT values.
How to fix Cannot Insert Explicit Value For Identity Column in Table in SQL Server
While working on the SQL Server databases, we need to perform various operations like insert, update, and delete data from the update which are famously known as OLTP operations. In the Insert operation, we developers sometimes face the error message saying – ‘Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF in SQL Server’ and in this article, we will understand the error message, what is IDENTITY_INSERT and how to reproduce the error and then solve the error.