In this blog post, let’s learn about the error message “120 – 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 Microsoft SQL Server, the reason why it appears and the solution to fix it.
SQL Server Error Message
120 – 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.
Reason for the Error
The SQL Server error message 120 – “The select list for the INSERT statement contains fewer items than the insert list” occurs when you try to perform an INSERT statement that includes a SELECT statement, and the number of columns returned by the SELECT statement does not match the number of columns specified in the INSERT statement.
For example, consider the following INSERT statement:
INSERT INTO MyTable (Column1, Column2, Column3) SELECT Column1, Column2 FROM MyOtherTable
In this example, the SELECT statement only returns values for Column1 and Column2, but the INSERT statement is trying to insert values into three columns: Column1, Column2, and Column3. This will result in the error message 120.
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 either add a default value for Column3 or remove it from the INSERT statement:
-- Add a default value for Column3 INSERT INTO MyTable (Column1, Column2, Column3) SELECT Column1, Column2, '' FROM MyOtherTable -- Remove Column3 from the INSERT statement INSERT INTO MyTable (Column1, Column2) SELECT Column1, Column2 FROM MyOtherTable
