Mysql – How to use the result of an insert as foreign key in another insert

insertMySQL

I have a temporary table with lots of data. I want to populate two persistent tables with excerpts of that data, preferably in one go.

Problem: after insert on tableA, I want to reuse the generated id as foreign key and add it to the insert for tableB. But is that possible at all?

Pseudocode for separated inserts:

INSERT INTO tableA (id, col1, col2, col3) 
    SELECT(col1, col2, col3) FROM tmp_table
    ON DUPLICATE KEY UPDATE id = last_insert_id(id);

INSERT INTO tableB (fk_id, col4)
    SELECT (fk_id_from_above, col4) FROM tmp_table
    ON DUPLICATE KEY UPDATE...

These are my tables:

//a simple temp table with lots of data
CREATE TABLE tmp_table (
 id bigint(20) NOT NULL AUTO_INCREMENT,
 col1 varchar(20) NOT NULL,
 col2 varchar(20) NOT NULL,
 col3 varchar(20) NOT NULL,
 col... varchar(20) NOT NULL,
 colN varchar(20) NOT NULL,
 PRIMARY KEY (id)
)

//this table should be filled from tmp_table and return the generated id for each row
CREATE TABLE tableA (
 id bigint(20) NOT NULL AUTO_INCREMENT,
 col1 varchar(20) NOT NULL,
 col2 varchar(20) NOT NULL,
 col3 varchar(20) NOT NULL,
 PRIMARY KEY (id)
)

//this table should reference the id from tableA (foreign key)
CREATE TABLE tableB (
 id bigint(20) NOT NULL AUTO_INCREMENT,
 fk_id bigint(20) NOT NULL,
 col4 varchar(20) NOT NULL,
 PRIMARY KEY (id)
)

I want to create a single INSERT, that first inserts into tableA, then reuses the generated id and persist it together with only some other columns in tableB.

Best Answer

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.

Related Question