Recently, while working with T-SQL's merge statement and its output clause I ran across an error I couldn't track down. My statement looked something like this:
MERGE #target_1 AS target USING (SELECT value1 FROM #source ) AS SOURCE ON target.value1 = SOURCE.value1 WHEN NOT matched THEN INSERT (value1) VALUES (value1) OUTPUT $action, inserted.* INTO #action_target;
The code was producing this error
Msg 213, Level 16, State 1, Line 25 COLUMN name OR NUMBER OF supplied VALUES does NOT MATCH TABLE definition.
Now, that's not an uncommon error. The easy, though time consuming, fix is to simply count the number of values in your field and value lists and figure out where the disconnect is. As you can see there is clearly one of each. The problem comes in during the output clause. Here's what the documentation has to say about the use of an asterisk.
* - Specifies that ALL COLUMNS affected BY the DELETE, INSERT, OR UPDATE action will be returned IN the ORDER IN which they exist IN the TABLE.
It turns out that this is an extremely literal statement. What I didn't recognize at the time was that while #target_1 was only having one value inserted into it there were actually 2 additional nulls being inserted. That made my output insert statement look more like this
INSERT INTO #action_target (@action, value1) VALUES ("Inserted", "a", NULL, NULL)
Which clearly doesn't line up. This reinforces the idea that asterisks, while useful shorthand can be problematic if not watched carefully.