Column name or number of supplied values does not match table definition
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:
[code language=”sql”]
DECLARE @T1 TABLE
(
ID INT,
SomeString VARCHAR(50)
)
UPDATE Table1
SET Column1 = null,
ModifiedDateTime = @DateTime,
ModifiedUserID = 123456
OUTPUT inserted.ID
‘Column1’
INTO @T1
from Table1
JOIN Table2
ON Table2.ID = Table1.ID
AND Table1.Column1 = @somevalue
[/code]
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 @T1, 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:
[code language=”sql”]
inserted.ID
‘Column1’
[/code]
See it now? I missed a comma after inserted.ID so it was generating an alias for the column called ‘Column1’.