? MySQL INSERT INTO SELECT

? Quick Overview

The INSERT INTO SELECT statement in MySQL allows you to insert records into a table by selecting data from another table. This approach eliminates the need to manually specify values and is extremely useful when working with large datasets, backups, historical data storage, and data synchronization between tables.

? Key Concepts

Copies data from one table directly into another table
Does not use the VALUES keyword
Number of selected columns must match inserted columns
Column data types must be compatible
Supports filtering using WHERE
Supports combining tables using JOIN
Can transform data using expressions and aliases

? Syntax / Theory

? View Code Example

// General syntax to insert records from one table into another
INSERT INTO target_table (column1, column2, column3)
SELECT column1, column2, column3
FROM source_table;

? Code Example(s)

? View Code Example

// Copy inactive users into an archive table
INSERT INTO users_archive (user_id, name, email)
SELECT id, name, email
FROM users
WHERE status = 'inactive';

? View Code Example

// Insert data using JOIN to combine multiple tables
INSERT INTO order_reports (order_id, customer_name, total_amount)
SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed';

? Live Output / Explanation

What happens internally?

MySQL first executes the SELECT query and retrieves the matching rows. Each returned row is then inserted into the destination table exactly as if it were manually inserted. The original table remains unchanged unless explicitly modified by another query.

If no rows match the WHERE condition, no data is inserted and no error occurs.

? Tips & Best Practices

Always run the SELECT query alone before executing INSERT
Ensure column order matches exactly between source and destination
Use transactions when inserting large volumes of data
Indexes on destination tables can slow down bulk inserts
Check for duplicate records to avoid constraint violations

? Try It Yourself / Practice Tasks

Create tables employees and employees_backup
Insert sample employee records
Copy employees from a specific department using WHERE
Modify the query to include calculated columns
Try inserting data using a JOIN