This article will discuss the error “Cannot insert explicit value for identity column in table
when IDENTITY_INSERT is set to OFF.” This error typically appears when you attempt to manually insert a value into an identity column while IDENTITY_INSERT is OFF.
To illustrate, first, create a database named “appuals.”
Next, create a table named “person” using the following code. This table includes a primary key column with an identity property:
To handle the error, learn to set the IDENTITY_INSERT option as shown below. This feature allows you to specify whether an explicit value can be inserted into an identity column:
The first argument, , is the name of the database containing the table. The second argument, , is the schema to which the table belongs. The third argument,
, specifies the table with the identity column in question.
There are two primary methods to insert data into a table without errors. Both methods serve as solutions to the error described earlier.
If you attempt to insert data with the IDENTITY_INSERT set to OFF, including a value for the identity column in the INSERT statement will trigger the error: “Cannot insert explicit value for identity column in table ‘person’ when IDENTITY_INSERT is set to OFF.”
Execute this code in the query tab:
This will result in the following error:
When IDENTITY_INSERT is OFF, exclude the identity column from the INSERT statement.
Execute this alternative code in the query tab:
This will successfully insert data without error. The IDENTITY property automatically generates a unique primary key ID, as shown below.
If you omit the ID value while IDENTITY_INSERT is set to ON, you’ll receive the error: “Msg 545, Level 16, State 1, Line 17. An explicit value must be specified for the identity column in table ‘person’ when IDENTITY_INSERT is set to ON.”
When IDENTITY_INSERT is ON, you must explicitly include the primary key ID in the INSERT statement.
Execute this code in the query tab:
This will insert data without error, requiring the user to specify the primary key ID directly. It will not auto-generate as it does when IDENTITY_INSERT is OFF.
When you SET IDENTITY_INSERT ON, it remains active for the entire session, allowing multiple records to be inserted. This setting is session-specific, so opening another query tab requires enabling it again for that window.
