MySQL Insert batch of records

Databases
21

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).

22

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.