INSERT with SELECT in MySQL

Introduction

You use INSERT with SELECT to copy data from one table to another quickly without typing each value.

When you want to move data from one table to another.

When you want to add rows based on a condition from another table.

When you want to duplicate some records with changes.

When you want to combine data from multiple tables into one.

When you want to back up some rows into another table.

Syntax

MySQL

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

The columns in INSERT and SELECT must match in number and type.

You can use WHERE in SELECT to choose which rows to insert.

Examples

Copy employees with salary over 50000 to the backup table.

MySQL

INSERT INTO employees_backup (id, name, salary)
SELECT id, name, salary
FROM employees
WHERE salary > 50000;

Move old orders before 2023 to an archive table.

MySQL

INSERT INTO archive_orders (order_id, order_date)
SELECT order_id, order_date
FROM orders
WHERE order_date < '2023-01-01';

Add customers from the West region to a new table.

MySQL

INSERT INTO new_customers (customer_id, customer_name)
SELECT customer_id, customer_name
FROM customers
WHERE region = 'West';

Sample Program

We create two tables: products and expensive_products. We add some products. Then we copy only products costing more than 40 into expensive_products. Finally, we show the copied rows.

MySQL

CREATE TABLE products (
  product_id INT,
  product_name VARCHAR(50),
  price DECIMAL(10,2)
);

CREATE TABLE expensive_products (
  product_id INT,
  product_name VARCHAR(50),
  price DECIMAL(10,2)
);

INSERT INTO products VALUES
(1, 'Pen', 1.20),
(2, 'Notebook', 3.50),
(3, 'Backpack', 45.00),
(4, 'Calculator', 55.00);

INSERT INTO expensive_products (product_id, product_name, price)
SELECT product_id, product_name, price
FROM products
WHERE price > 40.00;

SELECT * FROM expensive_products;

Important Notes

Make sure the target table exists before using INSERT with SELECT.

If columns are not listed, all columns must match in order and type.

You can use JOINs in the SELECT part to combine data from multiple tables.

Summary

INSERT with SELECT copies rows from one table to another.

Columns in both parts must match in number and type.

Use WHERE to filter which rows to insert.