Insert into Table Not Working - supplied values does not match

  • Hello Community,

    I watched demonstration which showed how to insert values into a table with a view to adding a row to an existing table with new values. The following is an image showing that the 'Insert' working and add an entityID of 207 insertworking

    When I run the same code on my SQL server (with tthe same number of columns) I get the error 'Column name or number of supplied values does not match table definition.' See image.

    insertnotworking

     

    Can someone let me know why I'm getting error whereas it worked during the demonstration?

     

    Thanks

  • Without DDL, we don't know what EntityID is.

     

    Try insert into config.entities (ListColumnNamesHere)

    select .......

    • This reply was modified 3 days, 7 hours ago by  Mike01.

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Hi Mike01

    Thanks for getting back to me:

    Here you go.

     

    CREATE TABLE [CP-PretBIHydr8Config].Config.Entities (

    EntityID INT NULL

    ,EntityName VARCHAR(250) NULL

    ,EntityType VARCHAR(50) NULL

    ,Classification INT NULL

    ,Area VARCHAR(150) NULL

    ,Domain VARCHAR(150) NULL

    ,Version INT NULL

    ,BusinessDomain VARCHAR(50) NULL

    ,Active BIT NULL

    )

     

    I just need to understand why I get the error, but the error didn't occur when it was demostrated during a  presentation?

    Thanks

     

  • As a random guess - I would say that in the presentation, entityID was an INT IDENTITY(1,1) and in your code, it doesn't seem to be an IDENTITY type.  So you are telling SQL you have a table with 9 columns and you are inserting into 8 of them.  What should SQL do with that column that isn't provided AND which column isn't being provided?  SQL has no idea.

    Now, if entityID is an IDENTITY type, SQL doesn't require a value for it as it will do it automatically.  It will actually give you errors if you do provide it unless you turn IDENTITY INSERT off on that table.

    Now, to add to Mike01's reply, if you provide the column names in the INSERT (ie "INSERT INTO table (column1, column2, column3..."), then if you have 9 columns total and you are only providing 8 of them, SQL can map them up.

    As for why you got the error and the demo didn't - the demo configured something different than you and I suspect that the demo had entityID as an INT IDENTITY (1,1) whereas you are just defining it as an INT.

  • Mr Brian Gale,

    Thanks so much for this, as this has been driving me crazy trying to understand why I'm getting the error.

    OK, I going to test it with INT IDENTITY (1,1)

    I hope you're right

  • Mr Brian Gale,

    You were spot on. Thanks so much mate

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply