I came across this familiar error recently when writing some scripts. Normally I see this error when doing an insert, and the number of columns you’ve specified doesn’t match the number of values you’ve provided. But this time it was in an UPDATE script.
Here’s the script:
DECLARE @AuditsToCreate TABLE ( tableName VARCHAR(50), ID INT, ColumnName VARCHAR(50), OldValue INT, NewValue INT ) UPDATE Table1 SET Column1 = null, ModifiedDateTime = @DateTime, ModifiedUserID = 123456 OUTPUT 'Table1', inserted.ID 'Column1', deleted.Column1, null INTO @AuditsToCreate from Table1 JOIN Table2 ON Table2.ID = Table1.ID AND Table1.Column1 = @somevalue
When this runs it gives an error
Column name or number of supplied values does not match table definition
I spent a bit of time looking for where the problem was. Can you see it?
The OUTPUT clause is inserting data into @AuditsToCreate, but there are 5 columns specified, and 5 columns in the table variable, so what’s the problem?
It took me a while to find, but I tracked it down to these two lines:
See it now? I missed a comma after inserted.ID so it was generating an alias for the column called ‘Column1’.