How to use the result of an insert as foreign key in another insert?
I think you can use the LAST_INSERT_ID() function, with something like this:

INSERT INTO tableA
    (col1, col2, col3) 
SELECT col1, col2, col3
FROM tmp_table
ORDER BY id ;

@first_id = LAST_INSERT_ID() - 1 ;

INSERT INTO tableB
    (fk_id, col4) 
SELECT @first_id = @first_id + 1, col4
FROM tmp_table
ORDER BY id ;


The ORDER BY id is needed in both insert statements because we want the rows from the temp table to be inserted in the same order to both tables.

The first insert (into tableA) will give consecutive id values to tableA.id column. The first of those values is the returned by the following LAST_INSERT_ID() call.

The second insert then uses that value (and increasing it one by one) to provide the same values (as tableA.id) to tableB.fk_id column.