Introduction

In MySQL, copying data from one table to another is usually done with INSERT ... SELECT. The exact statement depends on whether the destination table already exists, whether you want the schema copied too, and whether you are copying all rows or only a filtered subset.

Copy Data Into an Existing Table

If the destination table already exists and has compatible columns, use INSERT INTO ... SELECT:

INSERT INTO archive_orders (id, customer_id, total)
SELECT id, customer_id, total
FROM orders;

This copies data row by row inside MySQL without exporting anything to the client.

If the column order or names differ, be explicit on both sides. That is safer than relying on SELECT *, especially when schemas change over time.

You can also copy only part of the data:

1INSERT INTO archive_orders (id, customer_id, total)
2SELECT id, customer_id, total
3FROM orders
4WHERE created_at < '2024-01-01';

That is useful for archiving, migrations, or reporting snapshots.

Create the Destination Table First

If the destination table does not exist yet and you want the same structure, create it with LIKE:

1CREATE TABLE archive_orders LIKE orders;
2
3INSERT INTO archive_orders
4SELECT *
5FROM orders;

CREATE TABLE ... LIKE ... copies the column definitions and indexes, which makes it a common first step before an INSERT ... SELECT.

If you want to create a new table and populate it in one step, you can also use:

CREATE TABLE archive_orders AS
SELECT id, customer_id, total
FROM orders;

This is convenient, but it does not behave exactly like CREATE TABLE ... LIKE ... followed by insert. In practice, you often lose index definitions and other schema details, so use it when you want a quick data copy, not a structural clone.

Handling Duplicates and Existing Rows

If the target table may already contain some rows, you need to decide what duplicate behavior you want.

To ignore duplicates:

INSERT IGNORE INTO archive_orders (id, customer_id, total)
SELECT id, customer_id, total
FROM orders;

To update existing rows on key conflict:

1INSERT INTO archive_orders (id, customer_id, total)
2SELECT id, customer_id, total
3FROM orders
4ON DUPLICATE KEY UPDATE
5    customer_id = VALUES(customer_id),
6    total = VALUES(total);

These patterns matter when you are synchronizing tables rather than doing a one-time bulk copy.

Copying Safely

For larger copies, think about transaction boundaries and locking behavior. Even though the SQL is simple, the operational impact can be large if the source table is busy or the destination has heavy indexes and constraints.

A few practical habits help:

• copy explicit columns
• test the SELECT first
• count the rows before and after
• consider batching if the table is large

For example, preview the rows before inserting:

1SELECT id, customer_id, total
2FROM orders
3WHERE created_at < '2024-01-01'
4LIMIT 10;

That small step prevents accidental mass copies caused by a missing filter.

When to Use SELECT INTO OUTFILE Instead

Sometimes people ask this question when they really want export and re-import behavior. If the copy is within the same database and same server, INSERT ... SELECT is usually the right answer. SELECT INTO OUTFILE is for exporting data to files, not for direct in-database table-to-table copying.

So keep the operation inside MySQL unless you have a real reason to leave the server.

Common Pitfalls

• Using SELECT * when the source and destination column order may differ.
• Assuming CREATE TABLE ... AS SELECT ... copies indexes and constraints the same way LIKE does.
• Forgetting unique keys in the destination table, which can cause duplicate-key failures.
• Running a bulk copy without testing the SELECT clause first.
• Treating a large copy as a trivial query even though it may affect locks, transaction time, or replication lag.

Summary

• Use INSERT INTO ... SELECT to copy rows from one MySQL table to another.
• Use CREATE TABLE ... LIKE ... first if you want a structural clone of the source table.
• Use explicit column lists whenever possible.
• Choose duplicate behavior deliberately with plain insert, INSERT IGNORE, or ON DUPLICATE KEY UPDATE.
• For same-database copies, keep the operation inside MySQL instead of exporting to files unnecessarily.