• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL INSERT SELECT

← Previous Next →

The MySQL INSERT SELECT statement is a powerful feature that allows you to insert data into one table from another table using a single query. Unlike the traditional INSERT INTO ... VALUES statement, which requires you to manually insert rows, INSERT SELECT fetches data from an existing table, optionally transforming or filtering it, and inserts it directly into a target table.

This feature is commonly used for data migration, backup creation, report generation, or populating summary tables. It saves time and ensures data consistency when moving or copying large amounts of data in MySQL.

What Is INSERT SELECT?

In MySQL, INSERT SELECT combines the functionality of SELECT and INSERT. The data selected from a source table is inserted into a target table, either as-is or with modifications.

Key Points:

  • Allows inserting multiple rows at once.

  • Supports column selection or full table insertion.

  • Can filter rows using a WHERE clause.

  • Supports data transformation using expressions, functions, or joins.

  • Works seamlessly with aggregate functions and GROUP BY.

Basic Syntax

The basic syntax of INSERT SELECT is:

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;

Explanation:

  • target_table: Table where data will be inserted.

  • source_table: Table from which data is selected.

  • Columns must match in number and data type.

  • WHERE clause is optional to filter rows.

Example Tables

source_customers

customer_id customer_name city status
1 Ananya Delhi Active
2 Priya Jaipur Inactive
3 Isha Bangalore Active
4 Riya Chennai Active

target_customers

customer_id customer_name city status
(empty)      

Basic INSERT SELECT Example

To copy all active customers from source_customers to target_customers:

INSERT INTO target_customers (customer_id, customer_name, city, status)
SELECT customer_id, customer_name, city, status
FROM source_customers
WHERE status = 'Active';

Result in target_customers

customer_id customer_name city status
1 Ananya Delhi Active
3 Isha Bangalore Active
4 Riya Chennai Active

Step-by-Step Explanation:

  1. The SELECT statement retrieves all rows from source_customers where status = 'Active'.

  2. INSERT INTO copies those rows into target_customers.

  3. Column names and order must match to prevent errors.

INSERT SELECT with Expressions

You can modify data during insertion using expressions or literal values. For example:

INSERT INTO target_customers (customer_id, customer_name, city, status)
SELECT customer_id, customer_name, city, 'Pending'
FROM source_customers
WHERE city = 'Jaipur';

Result:

customer_id customer_name city status
2 Priya Jaipur Pending

Explanation:

  • The status column is set to 'Pending' for all inserted rows.

  • Expressions, functions, or literals can be used in the SELECT clause for transformations.

INSERT SELECT with Selected Columns

If the target table has fewer columns than the source table, you can insert only the required columns:

INSERT INTO target_customers (customer_name, city)
SELECT customer_name, city
FROM source_customers
WHERE status = 'Active';

Explanation:

  • Only the specified columns are inserted.

  • Columns not included will take their default value or remain NULL.

  • The order of columns in INSERT must match the SELECT clause.

INSERT SELECT with Joins

You can insert data from multiple tables using JOIN. Example:

INSERT INTO target_sales (customer_name, total_amount)
SELECT c.customer_name, SUM(o.amount)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name
HAVING SUM(o.amount) > 50000;

Explanation:

  • Joins customers and orders to calculate total sales per customer.

  • Only customers whose total sales exceed 50,000 are inserted.

  • Supports GROUP BY and HAVING to filter aggregated data.

INSERT SELECT with ORDER BY and LIMIT

You can control which rows are inserted by using ORDER BY and LIMIT:

INSERT INTO top_customers (customer_name, total_amount)
SELECT customer_name, SUM(amount)
FROM orders
GROUP BY customer_name
ORDER BY SUM(amount) DESC
LIMIT 5;

Explanation:

  • Inserts the top 5 customers by total sales into top_customers.

  • Useful for leaderboards, top performers, or prioritized reports.

Advanced Options: INSERT IGNORE and ON DUPLICATE KEY UPDATE

  1. INSERT IGNORE: Skips rows that would cause a duplicate key error.

INSERT IGNORE INTO target_customers (customer_id, customer_name, city, status)
SELECT customer_id, customer_name, city, status
FROM source_customers;

  1. ON DUPLICATE KEY UPDATE: Updates existing rows if a duplicate key is found.

INSERT INTO target_customers (customer_id, customer_name, city, status)
SELECT customer_id, customer_name, city, status
FROM source_customers
ON DUPLICATE KEY UPDATE status = VALUES(status);

Explanation:

  • Prevents errors when inserting rows with existing primary keys.

  • Updates specific columns without removing existing data.

Practical Real-World Scenarios

  1. Data Migration: Copy selected data from old tables to new ones during database upgrades.

  2. Backup Tables: Copy active records to an archive table for backup purposes.

  3. Summary Tables: Populate reporting tables with aggregated data from multiple tables.

  4. Data Transformation: Insert data with modified values or calculated columns into a new table.

  5. Leaderboard Creation: Insert top N performers based on scores, sales, or other metrics.

Common Mistakes to Avoid

  1. Column mismatch: Ensure the number of columns in INSERT matches SELECT.

  2. Data type mismatch: Source and target columns must be compatible in data type.

  3. Duplicate keys: Inserting rows with existing primary keys can cause errors unless using INSERT IGNORE or ON DUPLICATE KEY UPDATE.

  4. Missing WHERE clause: Without filtering, all rows from the source table are inserted, which may be unintended.

  5. Incorrect column order: The order of columns in INSERT must match the SELECT clause.

Tips for Optimizing INSERT SELECT

  • Use WHERE to filter only necessary rows.

  • Avoid selecting unnecessary columns.

  • Consider indexing columns used in joins for faster execution.

  • Use transactions for bulk inserts to ensure data integrity.

  • Monitor large inserts to prevent locking issues in production databases.

Summary of the Tutorial

The MySQL INSERT SELECT statement allows for efficient insertion of multiple rows from one table to another.

  • Combines SELECT and INSERT functionality in one query.

  • Supports filtering, transformations, aggregation, joins, and ordering.

  • Can be combined with INSERT IGNORE or ON DUPLICATE KEY UPDATE to handle duplicates.

  • Ideal for data migration, reporting, and backup tasks.

Mastering INSERT SELECT simplifies data management, improves efficiency, and ensures consistent results when working with large datasets in MySQL.

Practice Questions

  1. Write a query to copy all active customers from source_customers to target_customers.

  2. Using INSERT SELECT, insert only the customer_name and city of active customers into target_customers.

  3. Write a query to copy customers from source_customers and set their status to 'Pending' in target_customers.

  4. Using INSERT SELECT with a JOIN, insert customer names and their total order amounts into a target_sales table, including only customers whose total exceeds 50,000.

  5. Write a query to insert the top 3 customers by total amount from the orders table into a top_customers table using ORDER BY and LIMIT.

  6. Using INSERT SELECT, insert all rows from source_customers into target_customers, but ignore any duplicate primary keys.

  7. Write a query to insert aggregated product sales from the orders table into a product_summary table using GROUP BY.

  8. Using INSERT SELECT, copy data from source_customers into archive_customers where status = 'Inactive'.

  9. Write a query to insert data into target_customers from source_customers, and update the status if a duplicate customer_id exists.

  10. Using INSERT SELECT, copy only customers from a specific city (e.g., 'Delhi') into target_customers with a modified status 'Verified'.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top