Using Mysql to do multiple INSERT on linked tables
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);
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.
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@example.com', 1);