Skip to content

How to Batch INSERT INTO One Table from Another

MySQL does support batch insert into one table from another by the technique of subquery.

In this case, we'd like to move records that are more than 1 year old from a contemporary table orders to a historical table old_orders, which can make queries on orders faster than normal.
mysql> insert into old_orders (id, total_price, purchase_datetime) select id, total_price, purchase_datetime from orders where purchase_datetime < subdate(now(), interval 1 year);
Query OK, 8902 rows affected (0.31 sec)
Records: 8902  Duplicates: 0  Warnings: 0

Don't forget to delete those records from formal table orders.
mysql> delete from orders where purchase_datetime < subdate(now(), interval 1 year);
Query OK, 367 rows affected (0.19 sec)

Leave a Reply

Your email address will not be published. Required fields are marked *