0

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.

3
  • 1
    Does the temp table have 3 columns? (col1, col2, col3)? Does tableA have an autoincrement id column? It would be better if you showed the CREATE TABLE statements for both tableA, tableB and the temp table and some sample (temp) data. Sep 17, 2018 at 19:42
  • Does that SELECT produce only 1 row? If so, it might be possible. If not, it is messier. Please provide 'real' column names; they are often clues of your intent.
    – Rick James
    Oct 7, 2018 at 16:29
  • The select produces only a single result. Oct 7, 2018 at 20:52

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.