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:


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:


inserted.ID

'Column1',

See it now? I missed a comma after inserted.ID so it was generating an alias for the column called ‘Column1’.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s