MySQL Insert batch of records

  1. ‹ Older
  2. 4 months ago

    Duane M

    Mar 12 Pre-Release Testers, Xojo Pro Boston, MA

    It's a big speed difference. I've had 50,000 records to insert so it's the difference between 50 inserts and 50,000. Doing them individually was measured in hours vs. minutes for the batch.

    I should also mention that if I take that same INSERT string and put it into MySQL Workbench I get the same result. Just the last INSERT makes it into the database while none of those that precede it do.

  3. Tim P

    Mar 12 Pre-Release Testers, XDC Speakers

    @Duane M I should also mention that if I take that same INSERT string and put it into MySQL Workbench I get the same result. Just the last INSERT makes it into the database while none of those that precede it do.

    Well then, you'll need to post your *actual* SQL insert statement.
    Google says what you've got in your OP is correct: https://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql

  4. Dave S

    Mar 12 San Diego, California USA
    Edited 4 months ago

    Are you wrapping them in a TRANSACTION? if not you should....

    and/or try this syntax

    INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

    Not sure which DB you are using, but this works for mySQL... (it would insert 3 records)

    EDIT : oh, I see you found that syntax already :)

  5. Jean-Yves P

    Mar 12 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    @Tim P Google says what you've got in your OP is correct: https://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql

    it's true for any database.

  6. Duane M

    Mar 12 Pre-Release Testers, Xojo Pro Boston, MA
    Edited 4 months ago

    Here's a sample of what I have that is working. The field names are followed by 2 sets of VALUES each enclosed in () and separated by a comma and all terminated by a semicolon. I'm disguising the true contact info.

    INSERT INTO `doctors` (`doctor_class`,`primary_specialty`,`secondary_specialty`,`first_name`,`last_name`,`state`,`email`,`primary_phone`,`alt_phone`,`fax`,`street_address_1`,`street_address_2`,`city`,`zipcode`,`major_activity`,`facility`,`status`) VALUES ("x_test_data_only", "Dermatology", "", "Anita", "", "FL", "x@yahoo.com", "1112223333", "", "1112223333", "7125 Main Rd", "", "Somecity", "12345", "", "", "Active"),("x_test_data_only", "Allergy Immunology", "", "Gabriel", "Smith", "TX", "xx@yahoo.com", "1112223333", "1112223333", "1112223333", "123 Main St", "", "Smalltown", "12345", "", "", "Active");

    I can't find the exact code that was not working but it's 10 of these for testing. Each terminated with a semicolon. Here is a recreation. Note that I've tried various ways to enclose table name, field names, and data in " or ' or ` or nothing. INSERT INTO `doctors` (`doctor_class`,`primary_specialty`,`secondary_specialty`,`first_name`,`last_name`,`state`,`email`,`primary_phone`,`alt_phone`,`fax`,`street_address_1`,`street_address_2`,`city`,`zipcode`,`major_activity`,`facility`,`status`) VALUES ("x_test_data_only", "Allergy Immunology", "", "amy", "smith", "NY", "xx@usa.net", "", "", "", "123 Main St", "", "New York", "10028", "", "", "Active");INSERT INTO `doctors` (`doctor_class`,`primary_specialty`,`secondary_specialty`,`first_name`,`last_name`,`state`,`email`,`primary_phone`,`alt_phone`,`fax`,`street_address_1`,`street_address_2`,`city`,`zipcode`,`major_activity`,`facility`,`status`) VALUES ("x_test_data_only", "Allergy Immunology", "", "amy", "smith", "NY", "xx@usa.net", "", "", "", "123 Main St", "", "New York", "10028", "", "", "Active");

  7. Tim P

    Mar 12 Pre-Release Testers, XDC Speakers

    @Duane M INSERT INTO `doctors` (`doctor_class`,`primary_specialty`,`secondary_specialty`,`first_name`,`last_name`,`state`,`email`,`primary_phone`,`alt_phone`,`fax`,`street_address_1`,`street_address_2`,`city`,`zipcode`,`major_activity`,`facility`,`status`) VALUES ("x_test_data_only", "Dermatology", "", "Anita", "", "FL", "x@yahoo.com", "1112223333", "", "1112223333", "7125 Main Rd", "", "Somecity", "12345", "", "", "Active"),("x_test_data_only", "Allergy Immunology", "", "Gabriel", "Smith", "TX", "xx@yahoo.com", "1112223333", "1112223333", "1112223333", "123 Main St", "", "Smalltown", "12345", "", "", "Active");

    This works perfectly fine here. Are you sure that none of the actual values are breaking the SQL?

  8. Duane M

    Mar 12 Pre-Release Testers, Xojo Pro Boston, MA

    I just executed this in MySQL Workbench. With the exception of the first name in each the 2 are identical. They should both be inserted but only the last one is.

    INSERT INTO `doctors` (`doctor_class`,`primary_specialty`,`secondary_specialty`,`first_name`,`last_name`,`state`,`email`,`primary_phone`,`alt_phone`,`fax`,`street_address_1`,`street_address_2`,`city`,`zipcode`,`major_activity`,`facility`,`status`) VALUES ("x_test_data_only", "Pediatric Allergy", "", "First", "Record", "RI", "xxx@cox.net", "1112223333", "", "1112223333", "123 Main St", "", "Kingston", "2920", "", "", "Active"); INSERT INTO `doctors` (`doctor_class`,`primary_specialty`,`secondary_specialty`,`first_name`,`last_name`,`state`,`email`,`primary_phone`,`alt_phone`,`fax`,`street_address_1`,`street_address_2`,`city`,`zipcode`,`major_activity`,`facility`,`status`) VALUES ("x_test_data_only", "Pediatric Allergy", "", "Last", "Record", "RI", "againes@cox.net", "1112223333", "", "1112223333", "123 Main St", "", "Kingston", "2920", "", "", "Active");
  9. Duane M

    Mar 12 Pre-Release Testers, Xojo Pro Boston, MA

    @Tim P This works perfectly fine here. Are you sure that none of the actual values are breaking the SQL?

    Yes, that form of the insert does work here also. Not the one I just posted above and the one that also work elsewhere in my code. A puzzler.

  10. Duane M

    Mar 12 Pre-Release Testers, Xojo Pro Boston, MA
    Edited 4 months ago

    @Tim P Well then, you'll need to post your *actual* SQL insert statement.
    Google says what you've got in your OP is correct: https://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql

    It must be that in the code I'm building off of, the code that works as a string of INSERTS, does not specify the columns that the values go into becuase they are just going into the columns in order. In this case it's one INSERT with a string of values. Like it says here in the link.

  11. Tim P

    Mar 12 Pre-Release Testers, XDC Speakers

    @Duane M Yes, that form of the insert does work here also. Not the one I just posted above and the one that also work elsewhere in my code. A puzzler.

    I guess I've been confused the whole time - I thought single insert, multiple values is what you were aiming for.
    The double insert you posted works as well.

    So both of your fake data examples work, it must be the real data causing the problem.
    ¯\_(ツ)_/¯

  12. Tim P

    Mar 12 Pre-Release Testers, XDC Speakers

    Are you sure MySQL Workbench isn't reporting any errors?
    I don't use MySQL Workbench, but Squel Pro tells me how many rows were affected and how many errors occurred in the process. I can't imagine if rows are being dropped that no errors are being generated.

  13. Duane M

    Mar 12 Pre-Release Testers, Xojo Pro Boston, MA

    This data I'm testing with is test data that has already been accepted by the database. I'm just reusing the same data since it's only test data and can be deleted. I have verified that this version of the INSERT does not work in either MySQL Workbench or Sequel Pro. I tried with the same batch of 10 records, no errors reported, just 1 record entered.

    INSERT INTO Fruit (fruit_1, fruit_2, fruit_3) VALUES ('apple', 'orange', 'pineapple'); INSERT INTO Fruit (fruit_1, fruit_2, fruit_3) VALUES ('banana', 'mango', 'strawberry');

    I can verify that this version of the code does work. Note the difference is that the columns the data is going into is listed just once followed by an arbitrary number of record data groupings.

    INSERT INTO Fruit (fruit_1, fruit_2, fruit_3) VALUES ('apple', 'orange', 'pineapple'),('pear', 'mango', 'strawberries');
  14. Tim P

    Mar 12 Pre-Release Testers, XDC Speakers

    Both of those queries worked in Sequel Pro here.
    Perhaps you've got a weird setting on your table?

    This query works flawlessly here:

    DROP TABLE IF EXISTS fruit;
    CREATE TABLE `fruit` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `fruit_1` text CHARACTER SET latin1,
      `fruit_2` text CHARACTER SET latin1,
      `fruit_3` text CHARACTER SET latin1,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
    INSERT INTO Fruit (fruit_1, fruit_2, fruit_3) VALUES ('apple', 'orange', 'pineapple'),('pear', 'mango', 'strawberries');
    INSERT INTO Fruit (fruit_1, fruit_2, fruit_3) VALUES ('apple', 'orange', 'pineapple'); INSERT INTO Fruit (fruit_1, fruit_2, fruit_3) VALUES ('banana', 'mango', 'strawberry');
  15. Duane M

    Mar 12 Pre-Release Testers, Xojo Pro Boston, MA

    I'm running MySQL version 5.7.19. You?

  16. Tim P

    Mar 12 Pre-Release Testers, XDC Speakers

    5.7.16, Mac OS 10.11.6

  17. Duane M

    Mar 12 Pre-Release Testers, Xojo Pro Boston, MA

    I'm on AWS.

    I just ran your exact same code line for line. Same results, this does not work. Only inserts the last insert and no errors.

    INSERT INTO Fruit (fruit_1, fruit_2, fruit_3) VALUES ('apple', 'orange', 'pineapple'); INSERT INTO Fruit (fruit_1, fruit_2, fruit_3) VALUES ('banana', 'mango', 'strawberry');

    Looks to be something with AWS.

  18. Duane M

    Mar 12 Pre-Release Testers, Xojo Pro Boston, MA

    Just tried it on a 5.6.38 version of MySQL. I get the same results. Well that's about enough for me. I got something to work with and I've spent enough time on this.

    Thanks!

  19. Dave S

    Mar 12 San Diego, California USA

    Does the database table have any constraints? primary or foreign key violations? triggers? etc... etc....

  20. Tim P

    Mar 13 Pre-Release Testers, XDC Speakers

    @Dave S Does the database table have any constraints? primary or foreign key violations? triggers? etc... etc....

    That is why my test above destroys and recreates the table, to be sure of this. Duane ran my SQL and it failed (when it should not have).

  21. Duane M

    Mar 13 Pre-Release Testers, Xojo Pro Boston, MA

    Circling back to wrap this up. I ended up getting both versions of the INSERT to work. First this version.

    INSERT INTO Fruit (fruit_1, fruit_2, fruit_3) VALUES ('apple', 'orange', 'pineapple'),('pear', 'mango', 'strawberries');

    At first this did not work at all in Xojo but did in Workbench. So I knew I had a problem in my code. Turned out to be a wrongly placed "START TRANSACTION". I changed that and it worked.

    Then I had to look into the other form of the INSERT.

    INSERT INTO Fruit (fruit_1, fruit_2, fruit_3) VALUES ('apple', 'orange', 'pineapple'); INSERT INTO Fruit (fruit_1, fruit_2, fruit_3) VALUES ('banana', 'mango', 'strawberry');

    I tested the INSERT in Workbench and again only the last INSERT got into the database. Then it dawned on me...Workbench only looks back to the next preceding semicolon for the INSERT to run. Since I always put the cursor at the end it would only run the last INSERT in the string of combined INSERTs.

    So with a properly position START TRANSACTION and an improved INSERT string it worked.

    Thanks to all who helped and chimed in.

or Sign Up to reply!