stored procedures - Using Mysql to do multiple INSERT on linked tables

Question

I have two tables, one linked to the Primary Key of the other. At the moment I INSERT into table A, get the LAST_INSERT_ID, and then INSERT into table B.

But I have hundreds of records to insert and I want to speed things up.

In Mysql you can either:

INSERT INTO table_a (v1, v2, c3) VALUE (0, 1, 2);

INSERT INTO table_a (v1, v2, v3) VALUE (4, 5, 6); 

etc, or

INSERT INTO table_a (v1, v2, v3) VALUE (0, 1, 2), (4, 5, 6), etc to add multiple entries faster - but only for one table.

Of course the latter is much faster. I was wondering whether it was possible to replicate this behaviour for my example with two linked tables using a Stored Procedure, and whether it would have a similarly dramatic improvement in performance:

something like: call special_insert((0, 1, 2), (4, 5, 6), etc); or similar.

I have no Stored Procedure experience, so I'm fishing for ideas on which direction to proceed in.

Answer 1

Here is an example of a store procedure with a two table insert including Last_Insert_ID().

DELIMITER //
CREATE PROCEDURE new_person(
  first CHAR(35), last CHAR(35), email CHAR(255), tool_id INT)
BEGIN
START TRANSACTION;
   INSERT INTO person(firstname, lastname, email) 
     VALUES(first, last, email);

   INSERT INTO tasks (engineer_id, tool_id) 
     VALUES(LAST_INSERT_ID(), tool_id);
COMMIT;
END//
DELIMITER ;

CALL new_person('Jerry', 'Fernholz', '[email protected]', 1);