Saturday, June 18, 2022

The select list for the INSERT statement contains fewer items than the insert list

 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.

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.
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.

Here is the example of the code that cause the error.

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

Output:

 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.

You can fix this by correcting it.

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

You can use less columns this would depend on whether or not we have any NOT NULL constraints on the destination table.


No comments:

Post a Comment