CODEWITHSUNDEEP
sqlsql-server
Lefty
Lefty

Reputation: 391

SQL Server - "Column name or number of supplied value does not match table definition"

I know there have been a number of questions that address this issue in the past and I understand why I'm getting this error.

I'm really after a shortcut, my table has hundreds of columns and I don't want to have to manually sift through the code checking the value against its field definition. The error message when I try to run it always points to the same line number - the line with the INSERT on it.

Is there an easy way to get it to point to the actual field it has a problem with?

EDIT: After reading the answers given, I realised that I did NOT understand the cause of this message at all. It was JUST a result of a missing field and not related to incorrect data type at all. When I added the missing fields, the error went away even though the data type was still wrong in some fields.

Fortunately, the answers and comments understood the ACTUAL cause of the problem rather than my assessment of it, so the problem was solved.

Upvotes: 0

Views: 342

Answers (3)

Edper
Edper

Reputation: 9322

This is not an SQL solution. But I will probably make a small program just to map the field to values, and from there determine what's wrong.

Check my simple program in JavaScript that would map-field-to-values.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

One thing you can do is to structure the insert as:

insert into t(col1,
              col2,
              . . .
             )
    values (<whatever>,  -- col1
            <whatever>,  -- col2
            . . .
           );

This orders the expressions. You can find a problem by pasting the two lists into a spreadsheet and manually comparing them.

Or, as I'm more likely to do:

insert into t(col1,
              col2,
              . . .
             )
    select <whatever> as col1,
           <whatever> as col2
            . . .

Of course, merely listing the columns being inserted may solve your problem.

Then, I would suggest that you revisit your table. Having hundreds of columns in a table that is taking such inserts may be a bad idea. There may be a better way to structure the table.

For the curious, MySQL has an interesting, non-standard solution. It allows set in an insert, so you can explicitly set the column values.

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31785

Is there an easy way to get it to point to the actual field it has a problem with?

Of course not. That would require an intuitive understanding of the programmer's actual INTENT, of which computers are not currently capable.

You've got an insert like:

INSERT INTO MyTable (ColumnA, ColumnB, ... ColumnZ)
VALUES (ValueA, ValueC, ... ValueZ)

There's no way a computer could know that ColumnB should get ValueB and not ValueC, because maybe the programmer wanted it that way. Therefore there's no way to programmatically know that ValueB is the value that's missing, or that ColumnB is the "problem column".

So no, there's no shortcut, sorry.

Upvotes: 2

Related Questions