SQL Server Error 213

Error number

213

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 213 means an INSERT without a column list supplied a different number of values than the table has insertable columns, or INSERT ... EXEC received a result set of the wrong shape.

When an INSERT has no column list, SQL Server matches the supplied values to the table's columns by position, so the count must equal every column except identity and computed columns (in our tests a rowversion column still needed a value, such as DEFAULT). If the VALUES list, the SELECT or the procedure result has more or fewer columns, the statement fails with 213 at severity 16 and no row is inserted. The message names no column, so count the table's columns yourself.

With an explicit column list, a count mismatch raises a more specific error (109, 110, 120 or 121) instead, so 213 almost always points at an INSERT without a column list. A frequent trigger is a schema change: a column was added to the table and every positional INSERT written before the change breaks. If the extra value is meant for an identity column, SQL Server raises error 8101 rather than 213.

Common causes

• INSERT ... VALUES without a column list and fewer or more values than the table's columns.
• INSERT ... SELECT * from a staging or archive table whose columns no longer match the target.
• A column added to the table (ALTER TABLE ADD) after the INSERT code was written.
• A value supplied for a computed column, which cannot be inserted and is not counted.
• INSERT ... EXEC where the procedure returns more or fewer columns than the target table has.

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, TYPE_NAME(c.user_type_id) AS data_type,
       c.is_identity, c.is_computed, c.is_nullable
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.CustomersStaging;';

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 (id, name)
VALUES (1, N'Ana');

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

CREATE TABLE #List (id INT, name NVARCHAR(100), city NVARCHAR(50));

INSERT INTO #List (id, name, city)
EXEC dbo.usp_ListCustomers;

Examples

An INSERT without a column list after the table gained a column

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

INSERT INTO dbo.Customers VALUES (1, N'Ana');  -- 2 values, 3 columns

Msg 213, Level 16, State 1, Line 3
Column name or number of supplied values does not match table definition.

SELECT * from a staging table with an extra column

CREATE TABLE dbo.Customers (id INT PRIMARY KEY, name NVARCHAR(100));
CREATE TABLE dbo.CustomersStaging (id INT, name NVARCHAR(100), loaded_at DATETIME2);

INSERT INTO dbo.Customers
SELECT * FROM dbo.CustomersStaging;

Msg 213, Level 16, State 1, Line 4
Column name or number of supplied values does not match table definition.

A value supplied for a computed column

CREATE TABLE dbo.OrderLines (id INT PRIMARY KEY, qty INT, price DECIMAL(10,2),
                             line_total AS qty * price);  -- computed

INSERT INTO dbo.OrderLines VALUES (1, 2, 9.99, 19.98);

Msg 213, Level 16, State 1, Line 4
Column name or number of supplied values does not match table definition.

How to prevent it

Treat INSERT without a column list and INSERT ... SELECT * as fragile: they break, or worse, put values in the wrong columns when the types happen to match, as soon as a table changes. Always list target columns and source columns by name; code review checklists such as SQL code review best practices flag both patterns.

When you add a column to a table, search for positional INSERTs into it before deploying, and give the new column a default or allow NULLs so named INSERTs keep working.

Common questions

Learn more on CodeWithSQL

• DML commands lesson
• SQL code review best practices
• SQL best practices

Official documentation

• INSERT (Transact-SQL)
• sp_describe_first_result_set (Transact-SQL)
• Database engine events and errors (0 to 999)

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.