MySQL INSERT INTO SELECT

MySQL INSERT INTO SELECT: Bulk Copying Rows Between Tables

The MySQL INSERT INTO ... SELECT statement copies rows from a source query and inserts them directly into an existing target table in one server-side operation. Unlike exporting rows to application memory and running a loop of inserts, everything stays inside MySQL with minimal network overhead.

Basic Syntax

Make sure the column list after INSERT INTO matches the column count and data types returned by SELECT:

INSERT INTO target_table (col1, col2, col3)
SELECT col1, col2, col3
FROM source_table
WHERE filter_condition;

The target table must already exist before running INSERT INTO ... SELECT. If you want to create a brand-new table on the fly from a query result, use CREATE TABLE new_table AS SELECT ... instead.

Practical SQL Example

Suppose you want to archive inactive accounts from demo_customers into demo_archived_customers before cleaning up the main table:

-- Create the archive table once
CREATE TABLE IF NOT EXISTS demo_archived_customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    archived_at DATETIME NOT NULL
);

-- Copy inactive customers into the archive table
INSERT INTO demo_archived_customers (customer_id, first_name, last_name, email, archived_at)
SELECT id, first_name, last_name, email, NOW()
FROM demo_customers
WHERE is_active = 0;

Common Mistakes and Best Practices

• Preview the SELECT Query First: Run the exact SELECT ... FROM ... WHERE ... query on its own to verify row counts and column order before adding INSERT INTO.
• Handle Existing Keys Cleanly: If a batch job might re-run over overlapping rows, append ON DUPLICATE KEY UPDATE archived_at = VALUES(archived_at) or filter with NOT EXISTS so primary key collisions do not abort the batch.
• Chunk Large Copies to Reduce Locking: Copying millions of rows in one transaction locks source rows under repeatable-read isolation. Copy in primary-key ranges (WHERE id BETWEEN 1 AND 10000) and commit each chunk.

Frequently Asked Questions

Can I copy rows from a table into the same table?

Yes, INSERT INTO demo_products (product_name, price) SELECT CONCAT(product_name, ' Copy'), price FROM demo_products WHERE id = 1; duplicates a row safely in one statement.

Does INSERT INTO ... SELECT copy indexes and foreign keys?

No, it only inserts row data into an already-defined target table; indexes come from the target table's CREATE TABLE schema.

Can I combine JOIN and GROUP BY inside the SELECT part?

Yes, any valid SELECT query—including joins, aggregates, and LIMIT—works as the source for INSERT INTO ... SELECT.