In this blog post, let’s learn about the error message “121 – The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.
SQL Server Error Message
121 – The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.
Reason for the Error
The SQL Server error message 121 – “The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns” occurs when you try to perform an INSERT statement that includes a SELECT statement, and the number of columns returned by the SELECT statement is greater than the number of columns specified in the INSERT statement.
For example, consider the following INSERT statement:
INSERT INTO MyTable (Column1, Column2) SELECT Column1, Column2, Column3 FROM MyOtherTable
In this example, the SELECT statement returns values for three columns, but the INSERT statement is only trying to insert values into two columns: Column1 and Column2. This will result in the error message 121.
Solution
To fix this error, you need to ensure that the number of columns returned by the SELECT statement matches the number of columns specified in the INSERT statement. In the example above, you could modify the INSERT statement to include Column3:
INSERT INTO MyTable (Column1, Column2, Column3) SELECT Column1, Column2, Column3 FROM MyOtherTable
By modifying the INSERT statement to match the number of columns returned by the SELECT statement, you can avoid the error message 121.
