Inserting a select result into multiple tables

Question

I am trying to migrate data from an old table into two different tables. I am curious if there is a way to do something like the following.

INSERT INTO table1(v1, v2)
INSERT INTO table2(LAST_INSERT_ID(), v3)
SELECT v1, v2, v3 FROM old_table

Do I have to write a procedure for this?

That's not possible. I'd suggest you to either writing a procedure or running the query separately for each table you want to insert the values. — Matthias Burger, Aug 29 '16 at 12:02
[Mysql doesn't support muti-insert](http://stackoverflow.com/questions/3860280/sql-insert-into-multiple-tables-in-one-query) — adarsh hota, Aug 29 '16 at 12:03
@MatthiasBurger For the second insert I need the id from the 1st one. — kechap, Aug 29 '16 at 12:08
so write a procedure that solves this for you. you can get the id of the last inserted afaik with `LAST_INSERT_ID()` — Matthias Burger, Aug 29 '16 at 12:09

score 2 · Accepted Answer · answered Aug 29 '16 at 12:09

No, it's not possible. You need to issue two separate insert statements.

INSERT INTO table1(v1, v2)
SELECT v1, v2, v3, v4 FROM old_table;

INSERT INTO table2(v3, v4)
SELECT v1, v2, v3, v4 FROM old_table;

You could wrap this up in a transaction.

However, if you have more sophisticated need and that might be inserting the same data into multiple tables which you don't want to type by hand you could write a loop inside a procedure and execute dynamic statements providing table and column names. INFORMATION_SCHEMA tables would be a good place to start.

Inserting a select result into multiple tables

1 Answers1