5

I have a table, with two foreign keys defined on two columns of that table. The problem is that, when I insert the values from SQL Server, I get the following error:

Column name or number of supplied values does not match table definition

Please see the below image for the definition of the table. Can you please tell me why the value is not getting inserted?

Table Image

  • 1
    Please edit your question to show the INSERT statement, too. – Michael Green Dec 1 '14 at 3:46
  • @MichaelGreen: But I was getting this error only. – codeOholic Dec 1 '14 at 10:00
8

You're not showing us what you're doing - but based on the table structure, this is what you should do:

  • create an INSERT statement that explicitly lists the columns it will insert into - assuming that ID might be an IDENTITY column that you don't want / can't insert into

  • define the exact number of values to fill into these columns

So your INSERT statement should be something like:

INSERT INTO dbo.tbl_Post (cat_id, ngo_id, title, description, active)
VALUES (42, 4711, 'Some title', 'Some description', 1)

What you should definitely get in the habits of avoiding is using INSERT INTO dbo.tblPost without explicitly defining the list of column to insert into. This is just a recipe for disaster, as soon as you change your table definition, all your existing INSERTs will break since they don't match the table definition anymore.

Therefore: always explicitly define the list of columns that an INSERT statement should fill data into!

Also see Aaron Bertrand's excellent blog post on the topic:
Bad habits to kick: using SELECT * / omit the column list

  • I have two FK column in the tbl_Post. will it not afftect the table ? – codeOholic Nov 29 '14 at 10:47
  • @codeOholic: no - you just need to insert valid values for those two foreign keys.... – marc_s Nov 29 '14 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.