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

What it means

An IDENTITY column gets its values from SQL Server: 1, 2, 3… as rows are inserted. By default you can’t choose them yourself. Your INSERT named the identity column and gave it a value, so SQL Server refused the statement and inserted nothing.

Msg 544, Level 16, State 1, Server 7732422b7f56, Line 1
Cannot insert explicit value for identity column in table 'customers' when IDENTITY_INSERT is set to OFF.

Three errors come from the same rule:

Common causes

1. Copying rows with their ids: from another table, another database, a backup or a CSV export, with a column list that includes id (544), or with INSERT INTO t SELECT * FROM … and no column list (8101).
2. Seed or test data with fixed ids, so other rows can refer to them.
3. Application code that sends the key. An ORM or data layer that includes the id in its INSERT, often as 0, because its model doesn’t say the database generates that column.
4. A table made with SELECT … INTO. The copy keeps the IDENTITY property of a column copied as it is, so inserting ids into the copy fails too.
5. Changing an id with UPDATE (error 8102): SQL Server never allows it, with or without IDENTITY_INSERT.

How to fix it

Let SQL Server choose the id

Leave the identity column out of the column list, and read the new value back with OUTPUT (or SCOPE_IDENTITY() right after a single-row insert):

INSERT INTO dbo.customers (name, email)
OUTPUT inserted.id
VALUES (N'Joan', N'joan@example.com');

In application code, mark the key as generated by the database so the insert leaves it out.

Keep the ids on purpose with IDENTITY_INSERT

When the ids must stay the same (a migration, a restore of some rows, rows that other tables already point at), turn IDENTITY_INSERT on for the table, list every column, and turn it off again:

SET IDENTITY_INSERT dbo.customers ON;

INSERT INTO dbo.customers (id, name, email)
SELECT id, name, email FROM staging.customers;

SET IDENTITY_INSERT dbo.customers OFF;

What to know before you run it:

• The column list is required. INSERT … VALUES (…) or SELECT * without one is error 8101, even with the setting on.
• It lasts for your session only, and only one table per session can have it on at a time; turning it on for a second table fails with 8107 (IDENTITY_INSERT is already ON for table …).
• It needs permission: you must own the table or have ALTER on it. Without that, SQL Server says the table doesn’t exist (error 1088, Cannot find the object … because it does not exist or you do not have permissions.).
• The identity moves past your values. If you insert an id higher than the current identity value, the next generated id follows it. Inserting lower ids doesn’t move it, so a later generated id can collide with one you inserted (error 2627); check with DBCC CHECKIDENT ('dbo.customers', NORESEED).

Copy a table without its IDENTITY

SELECT * INTO copies the identity property. Wrap the column in an expression and the copy gets a plain int column you can insert into freely:

SELECT CAST(id AS int) AS id, name, email
INTO dbo.customers_copy
FROM dbo.customers;

Change an id by inserting a new row

An identity value can’t be updated. If you must renumber a row, insert a copy with the new id under IDENTITY_INSERT, move the rows that refer to it, and delete the old row, in one transaction. Usually it’s better to leave ids alone and add a separate column for any number people see.

Reproduce it

On SQL Server 2022 (16.0.4295.3) with sqlcmd, a table seo_err_mssql.customers (id int IDENTITY(1,1) PRIMARY KEY, name, email, created_at) with three rows:

INSERT INTO seo_err_mssql.customers (id, name) VALUES (10, N'Hedy');

Msg 544, Level 16, State 1, Server 7732422b7f56, Line 1
Cannot insert explicit value for identity column in table 'customers' when IDENTITY_INSERT is set to OFF.

With SET IDENTITY_INSERT seo_err_mssql.customers ON and INSERT … VALUES (10, …) without a column list; then UPDATE … SET id = 11; then turning it on for orders and customers in the same session:

Msg 8101, Level 16, State 1, Server 7732422b7f56, Line 2
An explicit value for the identity column in table 'seo_err_mssql.customers' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Msg 8102, Level 16, State 1, Server 7732422b7f56, Line 1
Cannot update identity column 'id'.
Msg 8107, Level 16, State 1, Server 7732422b7f56, Line 2
IDENTITY_INSERT is already ON for table 'inlet.seo_err_mssql.orders'. Cannot perform SET operation for table 'seo_err_mssql.customers'.

INSERT INTO seo_err_mssql.customers SELECT * FROM #src (a copy of its rows), with IDENTITY_INSERT off, gave the same 8101. SELECT * INTO #c FROM seo_err_mssql.customers and then inserting an id into #c gave 544 for table '#c'; with CAST(id AS int) AS id the copy had no identity column and the insert worked. Inside a transaction, with IDENTITY_INSERT on, the insert of id 10 with a column list worked, DBCC CHECKIDENT … NORESEED reported current identity value '10', and the next insert without an id got 11. After the ROLLBACK, IDENT_CURRENT was still 11: rolling back doesn’t give identity values back. The reader login, which can only read, got error 1088 for SET IDENTITY_INSERT. SQL Server 2019 and 2025 printed the same messages.

In Inlet

When SQL Server refuses, Inlet shows its message with SQL Server error 544, state 1, severity 16 and links to this page. A table’s definition shows its CREATE TABLE, so you can see which column is the IDENTITY.