CODEWITHSUNDEEP
mysql
kechap
kechap

Reputation: 2087

Inserting a select result into multiple tables

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?

Upvotes: 0

Views: 613

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

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.

Upvotes: 2

Related Questions