SQL Server error 213: 'Column name or number of supplied values does not match table definition'

Column name or number of supplied values does not match table definition. (error 213) means an INSERT without a column list sent a different number of values than the table has columns. It usually starts the day someone adds a column. Name the columns in the INSERT and the statement works again, whatever is added later.

Write INSERT INTO table (col1, col2) VALUES (...), never INSERT INTO table VALUES (...), in any code that will outlive today's schema.

The error

ALTER TABLE dbo.Customers ADD Phone nvarchar(20) NULL;
GO
INSERT INTO dbo.Customers VALUES (2, N'David Chen');

Msg 213, Level 16, State 1, Server MACHINE\LOCALDB#EAD42A01, Line 2
Column name or number of supplied values does not match table definition.

Why it happens

Without a column list, SQL Server matches the values to the table's columns by position, and expects a value for each of them. The table started with two columns, Id and FullName, and an INSERT ... VALUES (1, N'Maria Garcia') worked. Then a nullable Phone column was added. The table now has three columns, the old statement still supplies two, and SQL Server refuses it, even though the new column allows NULL and could simply have been left empty. Nothing is inserted.

The message does not say which column is missing or extra, so compare the value count with the table's columns. Here the table has three columns and the statement gives two values.

The fix

INSERT INTO dbo.Customers (Id, FullName) VALUES (2, N'David Chen');
SELECT Id, FullName, Phone FROM dbo.Customers ORDER BY Id;

The insert succeeded and the select returned both rows, 1 Maria Garcia NULL and 2 David Chen NULL: with an explicit list, the new column was left out and took its default, NULL. Named columns also make the statement say exactly which value goes where, so a reader does not need the table definition open to check it. Search the code base for INSERT INTO followed directly by VALUES after any schema change; each of those statements will break the same way the next time a column is added.

How it was reproduced

One script run with sqlcmd -S "(localdb)\MSSQLLocalDB" -d fix8_lab -I -i e213.sql -W on SQL Server 2022 LocalDB 16.0.1210.3 and sqlcmd 16.0.1000.6, in a throwaway database fix8_lab created for the test and dropped afterwards. A two-column customers table, one positional insert, an added nullable column, then the same positional insert again. The server's computer name in the message is replaced by MACHINE; nothing else changed.

Frequently asked

What does 'Column name or number of supplied values does not match table definition' mean?

SQL Server error 213 means an INSERT without a column list supplied a different number of values than the table has columns, often because a column was added later.

Why does my INSERT fail after adding a nullable column?

An INSERT without a column list must supply a value for every column, including the new one, even if it allows NULL. Name the columns in the INSERT and the new column takes its default.

How do I fix error 213 in SQL Server?

List the target columns explicitly, as in INSERT INTO dbo.Customers (Id, FullName) VALUES (...). The value count then has to match only the columns you named.

More decoded errors in the Fixes category.