6

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

James Rhoat
  • 1,577
  • 4
  • 12
  • 32
codeOholic
  • 63
  • 1
  • 1
  • 3

1 Answers1

9

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

marc_s
  • 9,052
  • 6
  • 46
  • 52