SQL Server error 120 occurs when you don’t specify enough columns in your INSERT list when using a SELECT list for the values to insert.

To be more specific, it happens when you use a SELECT list in your INSERT statement, but the SELECT list doesn’t return as many columns as you’re specifying with the INSERT.

This is easy to fix. Simply make sure the number of columns match between your INSERT and SELECT list.

Bad Code

Here’s an example of code that causes this error.

INSERT INTO OrdersLatest (
    OrderId, 
    OrderDate, 
    OrderDesc
    )
SELECT 
    OrderId,
    OrderDate
FROM OrdersMarch;

Result:

Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

In this case, I specify three columns in my INSERT, but I only select two columns in my SELECT list.

Good Code

Here’s how to fix the above error.

INSERT INTO OrdersLatest (
    OrderId, 
    OrderDate, 
    OrderDesc
    )
SELECT 
    OrderId,
    OrderDate, 
    OrderDesc
FROM OrdersMarch;

Result:

(5 rows affected)

All I did was add the OrderDesc column to the SELECT list.

Specifying Less Columns

The previous example obviously assumes that I wanted to insert the extra column. If we didn’t want that column to be inserted, then we should have removed it from the INSERT altogether.

Like this.

INSERT INTO OrdersLatest (
    OrderId, 
    OrderDate
    )
SELECT 
    OrderId,
    OrderDate
FROM OrdersMarch;

However, this would depend on whether or not we have any NOT NULL constraints on the destination table.

Using a Wildcard in the SELECT List

It’s also possible to use the asterisk (*) wildcard in the SELECT list.

INSERT INTO OrdersLatest (
    OrderDate, 
    OrderId, 
    OrderDesc
    )
SELECT *
FROM OrdersMarch;

Although this is not considered good practice, as you could accidentally select the wrong columns, or they could be in the wrong order, etc.