Introduction

Copying data into a new MySQL table can mean two different things: creating a new table with copied rows, or cloning the original table structure and then inserting the data. The right choice depends on whether you need indexes, defaults, and constraints copied along with the data.

Fastest Way: CREATE TABLE ... AS SELECT

If you just want a new table populated from a query result, use:

1CREATE TABLE archived_orders AS
2SELECT id, customer_id, total
3FROM orders
4WHERE created_at < '2025-01-01';

This creates archived_orders and fills it in one statement.

It is convenient, but it does not fully clone the original table definition. You usually do not get the original indexes, foreign keys, or all column metadata exactly as before.

Best Way When Structure Matters

If you want the new table to preserve the existing table structure more accurately, use CREATE TABLE ... LIKE first:

CREATE TABLE archived_orders LIKE orders;

Then copy the data:

1INSERT INTO archived_orders
2SELECT *
3FROM orders
4WHERE created_at < '2025-01-01';

This is usually the safer production approach because the new table starts with the same column definitions and indexes as the source table.

Copy Only Specific Columns

If the destination table has a different shape, list the columns explicitly:

1CREATE TABLE order_totals (
2    id INT PRIMARY KEY,
3    total DECIMAL(10, 2)
4);
5
6INSERT INTO order_totals (id, total)
7SELECT id, total
8FROM orders;

Explicit column lists are important when:

• Column order differs
• Some columns should be skipped
• The new table only stores a subset of the old data

Transform During the Copy

The copy query can also transform the data:

1CREATE TABLE customer_summary AS
2SELECT
3    customer_id,
4    COUNT(*) AS order_count,
5    SUM(total) AS total_spent
6FROM orders
7GROUP BY customer_id;

This is useful for reporting tables, archives, or denormalized snapshots.

Structure Copying Is Not All-Or-Nothing

This is the important distinction:

• 'CREATE TABLE ... AS SELECT is convenient for data shape produced by a query'
• 'CREATE TABLE ... LIKE is better when you care about the original schema details'

Even with LIKE, you should still confirm whether things such as triggers or environment-specific permissions need separate handling in your workflow. Schema copying and data copying are related, but they are not identical concerns.

Use Transactions Carefully

For large tables, copying can take time and lock resources depending on the storage engine and workload. If consistency matters, think about:

• Running during a maintenance window
• Copying in batches
• Using transactions where appropriate

For example, batch inserts can reduce operational risk:

1INSERT INTO archived_orders
2SELECT *
3FROM orders
4WHERE id BETWEEN 1 AND 100000;

Then repeat for the next range.

Common Pitfalls

• Using CREATE TABLE ... AS SELECT and expecting indexes and constraints to be preserved automatically.
• Using SELECT * when the destination schema may change later.
• Forgetting to filter rows and copying far more data than intended.
• Running a huge copy operation without considering locks, transaction size, or available disk space.

Summary

• Use CREATE TABLE ... AS SELECT for quick query-result copies.
• Use CREATE TABLE ... LIKE plus INSERT INTO ... SELECT when structure fidelity matters.
• List columns explicitly when schemas differ or when you only need part of the data.
• MySQL lets you transform data during the copy, not just duplicate it.
• The safest method depends on whether schema preservation or convenience is more important.