MySQL INSERT SELECT
Copying data from one table to another efficiently
📋 What is INSERT SELECT?
INSERT SELECT copies data from one table and inserts it into another table in a single operation. It's efficient for bulk data transfers, creating backups, or populating tables from query results without manual data entry.
-- Copy all customers from one table to another
INSERT INTO customers_backup (name, email, city)
SELECT name, email, city
FROM customers;
Result:
Query OK, 150 rows affected (0.05 sec)
Records: 150 Duplicates: 0 Warnings: 0
Key INSERT SELECT Concepts
Basic Copy
Copy all columns from one table to another
INSERT INTO new_table
SELECT * FROM old_table;Selective Copy
Copy specific columns only
INSERT INTO archive (id, name)
SELECT id, name
FROM users;Conditional Copy
Copy rows that meet conditions
INSERT INTO premium_users
SELECT * FROM users
WHERE subscription = 'premium';Transform Data
Modify data while copying
INSERT INTO summary (name, total)
SELECT name, SUM(amount)
FROM orders GROUP BY name;🔹 Basic INSERT SELECT
The simplest form of INSERT SELECT copies all data from one table to another. Make sure both tables have compatible column structures, or specify which columns to copy to avoid errors in your data transfer.
-- Copy all products to a backup table
INSERT INTO products_backup
SELECT * FROM products;
-- Copy specific columns
INSERT INTO customer_list (customer_name, email)
SELECT name, email_address
FROM customers;
Result:
Query OK, 500 rows affected (0.08 sec)
🔹 INSERT SELECT with WHERE Clause
Add a WHERE clause to copy only specific rows that meet your criteria. This is perfect for creating filtered subsets of data, archiving old records, or separating data based on status or date ranges.
-- Copy only active customers
INSERT INTO active_customers (id, name, email, status)
SELECT id, name, email, status
FROM customers
WHERE status = 'active';
-- Copy orders from last year
INSERT INTO orders_2023
SELECT *
FROM orders
WHERE YEAR(order_date) = 2023;
Result:
Query OK, 245 rows affected (0.04 sec)
🔹 INSERT SELECT with JOIN
Combine data from multiple tables using JOIN operations before inserting. This powerful technique lets you create summary tables, denormalized views, or reports that merge related information from different sources into one destination table.
-- Copy customer orders with customer names
INSERT INTO order_summary (order_id, customer_name, total_amount)
SELECT
o.id,
c.name,
o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed';
Result:
Query OK, 1,234 rows affected (0.12 sec)
🔹 INSERT SELECT with Aggregate Functions
Use aggregate functions like SUM, COUNT, AVG to calculate and insert summary data. This is ideal for creating reporting tables, daily summaries, or statistical snapshots that aggregate detailed transaction data into meaningful insights.
-- Create monthly sales summary
INSERT INTO monthly_sales (month, total_sales, order_count)
SELECT
DATE_FORMAT(order_date, '%Y-%m') as month,
SUM(total_amount) as total_sales,
COUNT(*) as order_count
FROM orders
WHERE YEAR(order_date) = 2024
GROUP BY DATE_FORMAT(order_date, '%Y-%m');
Result:
Query OK, 12 rows affected (0.06 sec)
🔹 Practical Examples
Real-world scenarios where INSERT SELECT saves time and effort. These examples show common database tasks like archiving old data, creating test datasets, migrating information, and building analytical tables from operational data efficiently.
-- Archive old records
INSERT INTO orders_archive
SELECT * FROM orders
WHERE order_date < DATE_SUB(NOW(), INTERVAL 1 YEAR);
-- Create a test dataset
INSERT INTO test_users (username, email)
SELECT username, email
FROM users
LIMIT 100;
-- Copy high-value customers to VIP table
INSERT INTO vip_customers
SELECT *
FROM customers
WHERE total_purchases > 10000;
-- Create product sales report
INSERT INTO product_report (product_name, units_sold, revenue)
SELECT
p.name,
COUNT(oi.id),
SUM(oi.price * oi.quantity)
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.name;