SQL Server Error 8101

Error number

8101

Severity

16 (user-correctable error)

State

1

Product

Microsoft SQL Server Database Engine

Versions

All supported SQL Server versions (2016 to 2025), Azure SQL Database, Azure SQL Managed Instance

Category

Syntax & Object Resolution

Last reviewed

23 October 2026

What it means

Error 8101 means an INSERT without a column list supplied a value for the identity column; SQL Server only accepts an explicit identity value when the INSERT names its columns and IDENTITY_INSERT is ON.

An explicit identity value has two requirements, and the message names both: a column list on the INSERT, and SET IDENTITY_INSERT table ON in the session. 8101 is raised when the column list is missing and the values line up with every column including the identity column, whether IDENTITY_INSERT is ON or OFF. It is severity 16 and no row is inserted. The table is shown with its schema, for example 'dbo.Customers'.

Compare it with error 544, the column-list form: there the INSERT names the identity column and only the setting is missing. So the quickest reading is: 8101 means "add a column list" (and the setting, if you want your own ids); 544 means "turn IDENTITY_INSERT ON". If the values do not even match the number of columns, you get error 213 instead.

Common causes

• SET IDENTITY_INSERT ON followed by INSERT ... VALUES without a column list, typical in hand-written migration scripts.
• INSERT INTO target SELECT * FROM source, copying between two tables that both have an identity column.
• A positional INSERT that includes a value for the id, written as if the identity column were an ordinary column.
• Generated scripts (data exports, test fixtures) that omit column lists.

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

SELECT c.column_id, c.name, c.is_identity, c.is_computed
FROM sys.columns AS c
WHERE c.object_id = OBJECT_ID(N'dbo.Customers')
ORDER BY c.column_id;

EXEC sp_describe_first_result_set
     N'SELECT * FROM dbo.Customers;';

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

INSERT INTO dbo.Customers (name)
VALUES (N'Ana');

SET IDENTITY_INSERT dbo.CustomersCopy ON;

INSERT INTO dbo.CustomersCopy (id, name)
SELECT id, name FROM dbo.Customers;

SET IDENTITY_INSERT dbo.CustomersCopy OFF;

Examples

IDENTITY_INSERT is ON, but the INSERT has no column list

CREATE TABLE dbo.Customers (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(100));
SET IDENTITY_INSERT dbo.Customers ON;
INSERT INTO dbo.Customers VALUES (10, N'Ana');

Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'dbo.Customers' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Copying with SELECT * between two identity tables

CREATE TABLE dbo.Customers (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(100));
CREATE TABLE dbo.CustomersCopy (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(100));
SET IDENTITY_INSERT dbo.CustomersCopy ON;
INSERT INTO dbo.CustomersCopy SELECT * FROM dbo.Customers;

Msg 8101, Level 16, State 1, Line 4
An explicit value for the identity column in table 'dbo.CustomersCopy' can only be specified when a column list is used and IDENTITY_INSERT is ON.

A positional INSERT that supplies the id (IDENTITY_INSERT OFF)

CREATE TABLE dbo.Customers (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(100));

INSERT INTO dbo.Customers VALUES (10, N'Ana');  -- 8101, not 544, because there is no column list

Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'dbo.Customers' can only be specified when a column list is used and IDENTITY_INSERT is ON.

How to prevent it

Always write a column list in INSERT statements, including in migration and fixture scripts. With a column list, any remaining problem is clearly reported (544 when the setting is missing) and the script keeps working when columns are added.

Avoid SELECT * as the source of an INSERT. For data copy jobs, generate an explicit list from sys.columns once and keep it in the script. The DML commands lesson covers INSERT syntax.

Common questions

Learn more on CodeWithSQL

• DML commands lesson
• SQL best practices
• Database migration checklist

Official documentation

• SET IDENTITY_INSERT (Transact-SQL)
• INSERT (Transact-SQL)
• Database engine events and errors (8000 to 8999)

Last technically reviewed 23 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.