MySQL Insert batch of records

I have some code that works well and will create a temporary table and insert records into it. It will batch them into groups of 1000 and then execute. It looks something like this.

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

The values go into the first 3 columns of the table and let’s say they are named fruit_1, fruit_2, fruit_3.

Now I am trying to do something similar into a permanent table where the first 3 columns are the primary key, creation date, and modification date and I need to skip those. So I have to name the columns I’m inserting the data into.

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

If I string a bunch of those together into a string and properly terminate each INSERT then the only record that gets into the database is the last one no matter how many precede it.
However if I do something like this it works.

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

I’m inclined to think there’s a commit issue but not sure. I’m just curious as to why it’s behaving that way. I’d be happy to hear suggestions. Thanks!

I’m not sure why that’s happening, but I wonder why you’re doing it this way? Is there a reason you can’t do single inserts in a loop, calling SQLExecute in the loop, instead of building up a long query?

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.

Well then, you’ll need to post your actual SQL insert statement.
Google says what you’ve got in your OP is correct: sql insert - Inserting multiple rows in mysql - Stack Overflow

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 :slight_smile:

it’s true for any database.

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. [code]INSERT INTOdoctors (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”);[/code]

[quote=377442:@Duane Mitchell]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");
[/quote]
This works perfectly fine here. Are you sure that none of the actual values are breaking the SQL?

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");

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.

[quote=377438:@Tim Parnell]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[/quote]
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.

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.
¯\(?)

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.

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');

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');

I’m running MySQL version 5.7.19. You?

5.7.16, Mac OS 10.11.6

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.

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!

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