Skip to content

How to Batch INSERT INTO One Table from Another

  • by
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.