Error message 213 is a common error that happens when you try to insert values into a table without explicitly specifying the column names.

The error looks like this:

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

It occurs when you specify the wrong number of values for that table. In other words, the number of values you provide doesn’t match the number of columns in the table.

Example

Here’s an example to demonstrate.

INSERT INTO Customers
VALUES ('Jake');

Result:

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

In my case, the problem is that the table actually contains three columns. My table definition looks like this:

CREATE TABLE Customers (
CustomerId int IDENTITY(1,1) NOT NULL,
FirstName nvarchar(255),
LastName nvarchar(255)
);

I’m trying to insert a value, but SQL Server doesn’t know which column it should go into, hence the error.

I would also get the same error if I tried to insert too many values. For example, the following also produces the same error.

INSERT INTO Customers
VALUES ('Jake', 'Smith', 'New York', 'USA');

Result:

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

How to Fix the Error

One way to fix this, is to ensure that the number of values you try to insert actually matches the number of columns in the table.

A better way to do it is explicitly specify the column names in your INSERT statement. Doing this will ensure you don’t accidentally insert data into the wrong columns.

So depending on which values I want to insert, I could rewrite my example to this:

INSERT INTO Customers (FirstName)
VALUES ('Jake');

Or this:

INSERT INTO Customers (FirstName, LastName)
VALUES ('Jake', 'Smith');

Implicit Column Names

As mentioned, it’s better to explicitly spell out each column name in your INSERT statement (as I did in the previous example).

I could however, change my example to use implicit column names, like this:

INSERT INTO Customers
VALUES (1, 'Jake', 'Smith');

However, this could now cause a separate issue regarding the identity column. See How to Insert an Explicit Value into an Identity Column if you need to do this.