Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF

sql

identity-insert

database-management

sql-best-practices

byAlex Kataev·Oct 4, 2024

To explicitly insert a value into an identity column, switch on IDENTITY_INSERT, implement the INSERT, and finally turn it OFF. Here's how you do it in SQL:

SET IDENTITY_INSERT YourTable ON;
-- SQL Server : "Okay, just this once..."
INSERT INTO YourTable (IdentityColumn, ...) VALUES (DesiredValue, ...);
-- Database: "Hey, that's my job!" 
SET IDENTITY_INSERT YourTable OFF;
-- Database, relieved: "Ah, much better. I'm back in the driver's seat."

Where 'YourTable' is the name of your table, 'IdentityColumn' is the identity column, and 'DesiredValue' is the value you want to insert. This essentially overrides the auto-generated identity values.

When to resort to IDENTITY_INSERT

In rare cases

Identity columns are designed to auto-generate values, so use IDENTITY_INSERT mostly for data migrations or unusual data manipulations.

Single-table operations

IDENTITY_INSERT only affects a single table. If you have multiple tables needing explicit value inserts, run the command for each table.

Entity Framework context

If you're using Entity Framework, remember to update your .edmx file to reflect any IDENTITY_INSERT settings change.

Best practice around IDENTITY_INSERT

Detailed Insert statement

While IDENTITY_INSERT is ON, include all the columns in your INSERT statements. This avoids errors from assuming column order.

Single user mode

Set database to single user mode, or restrict access when using IDENTITY_INSERT, to prevent potential chaos from concurrent inserts.

Matching Entity Framework behavior

If leveraging Entity Framework, control identity columns' behavior using attributes such as [DatabaseGenerated(DatabaseGeneratedOption.Identity)].

Things to watch out for

Identity shenanigans in web apps

Before attempting identity column choreography in a web app, set up proper error handling. This covers for instances where IDENTITY_INSERT remains ON, blocking regular operations.