T-SQL – select items NOT in the list and a mysterious error message 8158

What if you have list in Excel and you need to validate what items on that list do not exist in a table.

Proper syntax will be like this:

SELECT TempList.DOCO
FROM (VALUES(13839),(14339),(18892),(22201),(29776),(32008),(33331),(33567),(49585),(55386),(59600),(75352),(96535),(121089))
TempList(DOCO)
WHERE DOCO NOT IN (SELECT CHDOCO FROM F1720)

Here you are validating your list of against contracts in table F1720.

However, if you do not enclose each value in the list into parenthesis, you will get this error message:

Msg 8158
'TempList' has more columns than were specified in the column list.

When you enclose set of values in parenthesis, you indicating a row of data. Without parenthesis it is a continuations of the valies in one row of data.