How do you use MySQL's INSERT INTO SELECT with Common Table Expressions (CTE)?

MySQL's INSERT INTO SELECT statement can be combined with Common Table Expressions (CTEs) to insert data into a table based on the results of a CTE. This allows for more complex data manipulation and insertion scenarios. Here's how you can use them together:

Basic Syntax:

The general structure involves defining a CTE using the WITH clause, and then using the results of that CTE in an INSERT INTO SELECT statement.

WITH cte_name AS (
  SELECT column1, column2, ...
  FROM source_table
  WHERE condition
)
INSERT INTO target_table (column_a, column_b, ...)
SELECT column1, column2, ...
FROM cte_name;

Explanation:

1. Define the CTE: The WITH clause introduces a CTE named cte_name. This CTE is defined by a SELECT statement that retrieves data from source_table, potentially with a WHERE clause to filter the data.

2. Insert into Target Table: The INSERT INTO statement specifies the target_table and the columns into which data will be inserted. The SELECT statement following the INSERT INTO clause retrieves data from the CTE (cte_name) and inserts it into the target_table.

3. Column Mapping: Ensure that the columns selected in the CTE's SELECT statement match the columns specified in the INSERT INTO statement in terms of order and data type.

Example:

Suppose you have a table named employees and you want to insert a subset of employees into a new table named managers based on their department. Here's how you can do it using a CTE:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  department VARCHAR(255)
);

CREATE TABLE managers (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  department VARCHAR(255)
);

INSERT INTO employees (id, name, department) VALUES
(1, 'John Doe', 'Sales'),
(2, 'Jane Smith', 'Marketing'),
(3, 'Peter Jones', 'Sales'),
(4, 'Alice Brown', 'IT');

WITH sales_managers AS (
  SELECT id, name, department
  FROM employees
  WHERE department = 'Sales'
)
INSERT INTO managers (id, name, department)
SELECT id, name, department
FROM sales_managers;

In this example, the CTE sales_managers selects all employees from the 'Sales' department. The INSERT INTO SELECT statement then inserts these employees into the managers table.

Benefits of Using CTEs with INSERT INTO SELECT:

- Readability: CTEs make complex queries easier to read and understand by breaking them down into logical parts.
- Reusability: CTEs can be referenced multiple times within the same query, avoiding redundant code.
- Modularity: CTEs allow you to build complex data transformations step-by-step, making the overall query more manageable.

By using CTEs with INSERT INTO SELECT, you can perform sophisticated data manipulations and insertions in a clear and efficient manner.

Learn more about this