In this article, we will discuss the error “Cannot insert explicit value for identity column in table

when IDENTITY_INSERT is set to OFF” as shown below. The error arises when the user has set “identity_insert” to “OFF”. Then tries to insert data into the primary key column of the table explicitly. This can be explained using the example below.

Database and table creation:

First, create a database named “appuals”. Create a table named “person” using the following code. Built table using a “PRIMARY KEY IDENTITY”

Syntax for setting “identity_insert off | on”:

The “set identity_insert off | on” will help us resolve this error. The correct syntax for this statement is as below. Whereas the first argument is the name of the database in which the table is located. The second argument shows is the schema to which that table belongs whose identity value has to be set to ON or OFF. The third argument

is the table with the identity column. There are fundamentally two different ways of data insertion into the table without error. These are considered as the solution to this error and are discussed below.

Error 1:  Set identity_insert OFF

In the first case, we will insert data into the table with the “IDENTITY INSERT” set to “OFF”. So, if the ID is present into the INSERT statement, you will get the error “Cannot insert explicit value for identity column in table ‘person’ when IDENTITY_INSERT is set to OFF”. Execute the following code in the query tab. The output will be like this.

Solution:

When turning the “IDENTITY INSERT OFF”, the “PRIMARY KEY ID” MUST NOT be PRESENT into the insert statement Now execute the following code in the query tab This will insert data into the table without an error. Moreover, The PRIMARY KEY ID is not required to be inserted by the user, rather it will add unique ID value automatically as seen in the figure below.

Error 2:  Set identity_insert ON

In the second case, we will insert data into the table with the “IDENTITY INSERT” set to “ON”. So, if the ID is not present into the INSERT statement, you will get the error ” Msg 545, Level 16, State 1, Line 17. The explicit value must be specified for identity column in table ‘person’ either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column”.

Solution:

When turning the “IDENTITY INSERT ON” the “PRIMARY KEY ID” must be present in the insert statement. Execute the following code in the query tab This will insert data into the table without an error. Thus The PRIMARY KEY ID is explicitly required to be inserted by the user. Also, it will not add unique ID value automatically as seen in the figure below. If you “SET IDENTITY INSERT ON”, it will remain on for the whole session. Thus you can add as many records as you want once this has been set. This also refers only to the session where it is enabled. So if you open another query tab you need to turn it ON again for that query window.

AMD Removes CrossFire Support In Upcoming Navi GPUs, In Favor Of A More…Fix: Please Insert the Last Disk of the Multi-Volume SetHow to Fix “Cannot Verify Server Identity Error” on iPhone?Fix: The Identity of This Website or the Integrity of this Connection Cannot be… How to Fix the Error Cannot insert explicit value for identity column in table when IDENTITY INSERT is set to OFF - 29How to Fix the Error Cannot insert explicit value for identity column in table when IDENTITY INSERT is set to OFF - 34How to Fix the Error Cannot insert explicit value for identity column in table when IDENTITY INSERT is set to OFF - 78How to Fix the Error Cannot insert explicit value for identity column in table when IDENTITY INSERT is set to OFF - 26How to Fix the Error Cannot insert explicit value for identity column in table when IDENTITY INSERT is set to OFF - 22How to Fix the Error Cannot insert explicit value for identity column in table when IDENTITY INSERT is set to OFF - 88How to Fix the Error Cannot insert explicit value for identity column in table when IDENTITY INSERT is set to OFF - 73