如何为主表和辅助表编写SQL多插入查询

Question

I have two tables and I want to write a multi insert query such that, the PK from first table is auto inserted as fk in another table.

Let us assume following table schemas:

tblParent

ParentID (Primary Key Auto increment)
ParentValue

tblChild

ChildID (Primary Key Auto increment)
FkParentID (Foreign Key)
ChildValue

Now I want to write a multi insert query like following

INSERT INTO tblParent, tblChild (ParentValue, FkParentID, ChildValue) VALUES ('Parent 1', <ParentID of 'Parent 1'>, 'Child 1'), ('Parent 2', <ParentID of 'Parent 2'>, 'Child 2'), ('Parent 3', <ParentID of 'Parent 3'>, 'Child 3')

But I do not know how to do this. I have millions of records to be inserted in these tables. And I do not want to insert a record in first table, then fetch parent id and insert records in second table.

Edit 1: My Database is Mysql InnoDb

Edit 2: The tables in question have one-to-many relationship. So this means I want to perform one insert in tblParent and multiple inserts into tblChild for each record in tblParent

Edit 3:

I was looking in the MySql documentation and came across following description:

https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id

The currently executing statement does not affect the value of LAST_INSERT_ID(). Suppose that you generate an AUTO_INCREMENT value with one statement, and then refer to LAST_INSERT_ID() in a multiple-row INSERT statement that inserts rows into a table with its own AUTO_INCREMENT column. The value of LAST_INSERT_ID() will remain stable in the second statement; its value for the second and later rows is not affected by the earlier row insertions. (However, if you mix references to LAST_INSERT_ID() and LAST_INSERT_ID(expr), the effect is undefined.)

So my next question is, how would LAST_INSERT_ID behave in case of multi insert for tblParent which in turn has multiple inserts in tblChild for each tblParent

Answer 1

Anyways, for now what I did is used multi_query to insert values.

I did following inside a while loop:

$autoIncForParent = <Max ID from tblParent>;
while(<condition to evaluate>) {
    if (!empty($multiQuery)) {
        $multiQuery .= ";";
    }
    $multiQuery .= "INSERT INTO tblParent (ParentID , ParentValue) VALUES ($autoIncForParent, 'Parent 1');";
    $multiQuery .= "INSERT INTO tblChild (FkParentID , ChildValue) VALUES ($autoIncForParent, 'Child 1');";
$autoIncForParent++;
}
$mysqli->multi_query($multiQuery);

I am pretty sure there must be another good way of solving this.

I have taken care of memory issues, race conditions and what nots in my actual code. This snippet is for elaboration purpose only. By no means this snippet is perfect and I would not recommend this for production.

Edit 1: Added missing semicolons. In case someone refers this