mysql: is there a way to do a "INSERT INTO" 2 tables?

I have one table with 2 columns that i essentially want to split into 2 tables:

开发者_JAVA百科

table A columns: user_id, col1, col2

New tables:

B: user_id, col1

C: user_id, col2

I want to do:

INSERT INTO B (user_id, col1) SELECT user_id,col1 from A;
INSERT INTO C (user_id,col2) SELECT user_id, col2 from A;

But i want to do it in one statement. The table is big, so i just want to do it in one pass. Is there a way to do this?

Thx.

---

No, you can't insert into more than one table at the same time. INSERT syntax allows only a single table name.

http://dev.mysql.com/doc/refman/5.5/en/insert.html

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [...

---

Write a stored procedure to encapsulate the two inserts and protect the transaction.

---

If by "in one statement", you mean "atomically" - so that it can never happen that it's inserted into one table but not the other - then transactions are what you're looking for:

START TRANSACTION;
INSERT INTO B (user_id, col1) SELECT user_id,col1 from A;
INSERT INTO C (user_id,col2) SELECT user_id, col2 from A;
COMMIT;

If you need to actually do this in a single statement, you could create these as a stored procedure and call that, as @lexu suggests.

See the manual for reference: http://dev.mysql.com/doc/refman/5.0/en/commit.html

Caveat: this will not work with MyISAM tables (no transaction support), they need to be InnoDB.

---

Unless your tables are spread over multiple physical disks, then the speed of the select/insert is likely to be IO bound.

Trying to insert into two tables at once (even if it were possible) is likely to increase the total insert time as the disk will have to thrash more writing to your tables.