MySQL - Insert Into Select

MySQL: Copying Data with INSERT INTO SELECT

The INSERT INTO ... SELECT statement is a powerful command for copying data from one table to another. It’s highly efficient because the data transfer happens entirely within the database server. This is commonly used for archiving old data, creating summary tables, or populating test environments with production data.

Basic Syntax

The statement combines an INSERT INTO with a SELECT. The SELECT query retrieves the data, and the INSERT INTO part specifies where to put it.

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

Key points to remember:

• The target table must exist before you run the command.
• The data types of the columns in the SELECT list must be compatible with the data types of the columns in the INSERT list.
• The number of columns must match between the SELECT and INSERT lists.

Example: Archiving Old Orders

Let’s imagine we have an orders table that’s getting very large. We want to move all completed orders older than one year into an orders_archive table to keep the main table smaller and faster.

First, let’s set up our source and target tables.

-- The main table with current and recent orders
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'shipped', 'completed', 'cancelled') NOT NULL
);

-- The archive table with an identical structure
CREATE TABLE orders_archive (
    id INT PRIMARY KEY, -- Note: NOT AUTO_INCREMENT here
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'shipped', 'completed', 'cancelled') NOT NULL
);

-- Insert some sample data into the main orders table
INSERT INTO orders (customer_id, order_date, total_amount, status) VALUES
(101, '2023-10-25', 150.75, 'shipped'),
(102, '2022-05-10', 99.50, 'completed'),  -- This one should be archived
(103, '2023-11-01', 24.00, 'pending'),
(101, '2022-03-15', 305.00, 'completed'); -- This one should also be archived

Now, we can use INSERT INTO ... SELECT to copy the old, completed orders.

INSERT INTO orders_archive (id, customer_id, order_date, total_amount, status)
SELECT id, customer_id, order_date, total_amount, status
FROM orders
WHERE status = 'completed' AND order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

-- After copying, you would typically delete the archived rows from the main table
-- DELETE FROM orders
-- WHERE status = 'completed' AND order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

Advanced Usage: Handling Duplicates and Mismatched Columns

Ignoring Duplicates

If you run the same archive script twice, it will fail because of a duplicate primary key id. You can use INSERT IGNORE to tell MySQL to simply discard rows that would cause a duplicate key violation.

INSERT IGNORE INTO orders_archive (...)
SELECT ... FROM orders WHERE ...;

Updating on Duplicate

A more powerful option is ON DUPLICATE KEY UPDATE. This is used for ‘upsert’ logic: if a row with the same unique/primary key exists, update it; otherwise, insert it. This is great for creating and updating summary tables.

-- Imagine a table summarizing total sales per customer
CREATE TABLE customer_summary (
    customer_id INT PRIMARY KEY,
    total_spent DECIMAL(12, 2) NOT NULL,
    last_order_date DATE NOT NULL
);

-- This statement can be run daily to update the summary
INSERT INTO customer_summary (customer_id, total_spent, last_order_date)
SELECT customer_id, SUM(total_amount), MAX(order_date)
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
ON DUPLICATE KEY UPDATE
    total_spent = customer_summary.total_spent + VALUES(total_spent),
    last_order_date = VALUES(last_order_date);

Transforming Data During Insert

The SELECT statement doesn’t have to be a direct column-for-column copy. You can use expressions, literals, and functions to transform the data as it’s inserted.

-- Let's create a 'mailing_list' from our customers
CREATE TABLE mailing_list (
    email VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255),
    subscribed_on DATETIME NOT NULL
);

-- Assume we have a 'customers' table with 'id', 'name', 'email'
INSERT INTO mailing_list (email, name, subscribed_on)
SELECT email, name, NOW() -- Insert the current timestamp
FROM customers
WHERE has_consented = TRUE;

Practical Application: Data Migration Scripts

While you can run these queries directly, they are often part of larger scripts written in languages like Python, Node.js, or PHP. Here’s how you might execute our order archiving task in a modern Node.js script.

Node.js Example using mysql2/promise

This example uses async/await for clean, readable code and a transaction to ensure that data is either copied and deleted successfully, or neither operation happens.

// Setup: npm install mysql2 dotenv
// Create a .env file with your DB credentials:
// DB_HOST=localhost
// DB_USER=your_user
// DB_PASSWORD=your_password
// DB_NAME=your_db

require('dotenv').config();
const mysql = require('mysql2/promise');

async function archiveOldOrders() {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: process.env.DB_HOST,
            user: process.env.DB_USER,
            password: process.env.DB_PASSWORD,
            database: process.env.DB_NAME
        });

        await connection.beginTransaction();
        console.log('Transaction started.');

        const archiveSql = `
            INSERT INTO orders_archive (id, customer_id, order_date, total_amount, status)
            SELECT id, customer_id, order_date, total_amount, status
            FROM orders
            WHERE status = 'completed' AND order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
        `;

        const [result] = await connection.execute(archiveSql);
        console.log(`${result.affectedRows} rows archived.`);

        if (result.affectedRows > 0) {
            const deleteSql = `
                DELETE FROM orders
                WHERE status = 'completed' AND order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
            `;
            const [deleteResult] = await connection.execute(deleteSql);
            console.log(`${deleteResult.affectedRows} rows deleted from main table.`);
        }

        await connection.commit();
        console.log('Transaction committed successfully.');

    } catch (error) {
        console.error('Error during archiving process:', error);
        if (connection) {
            await connection.rollback();
            console.log('Transaction rolled back.');
        }
    } finally {
        if (connection) {
            await connection.end();
            console.log('Connection closed.');
        }
    }
}

archiveOldOrders();