This is a commonly encountered error in SQL Server when inserting data into a table. The full error goes like this:

Msg 109, Level 15, State 1, Line 1
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

This happens when you specify more columns in the INSERT statement than the number of values that you’re trying to insert with the VALUES clause.

This will occur if you accidentally omit one or more values from the VALUES clause.

You’d get a similar (but technically different) error if you tried to do the opposite – specify fewer columns in the INSERT statement than you try to insert.

Example

Here’s an example to demonstrate.

INSERT INTO Customers (FirstName, LastName) 
VALUES ('Bob');

Result:

Msg 109, Level 15, State 1, Line 1
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

In this case, I specify two columns (FirstName, LastName), but I only specify one value to insert (Bob).

How to Fix the Error

I can fix this by either removing one of the columns, or adding a second value to be inserted.

So I could do this:

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

Or this:

INSERT INTO Customers (FirstName, LastName) 
VALUES ('Bob', 'Brown');

Although, using these examples, if the LastName column has a NOT NULL constraint, the first example will violate that constraint (because I’d be attempting to insert NULL into the LastName column when there’s actually a NOT NULL constraint on that column).