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

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.

Answer: 
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](https://stackoverflow.com/questions/4859774/mysql-is-there-a-way-to-do-a-insert-into-2-tables/4859806#4859806) 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.