Post reply

Insert into Table Not Working - supplied values does not match

  • July 21, 2021 at 7:28 pm

    #3912563

    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

  • July 21, 2021 at 7:49 pm

    #3912573

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

     

    Try insert into config.entities (ListColumnNamesHere)

    select .......

    • This reply was modified 2 years, 9 months 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/

  • July 21, 2021 at 8:47 pm

    #3912602

    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

     

  • July 21, 2021 at 9:05 pm

    #3912616

    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.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • July 21, 2021 at 9:23 pm

    #3912619

    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

  • July 21, 2021 at 9:28 pm

    #3912621

    Mr Brian Gale,

    You were spot on. Thanks so much mate

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

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