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
.
id
column? It would be better if you showed theCREATE TABLE
statements for both tableA, tableB and the temp table and some sample (temp) data.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.