sql – Using the WITH clause in an INSERT statement

In SQL, the WITH clause, also known as a Common Table Expression (CTE), can be used in conjunction with an INSERT statement to simplify complex queries. The WITH clause allows you to define a temporary result set that can be referenced within the INSERT statement.

Here’s how you can use the WITH clause in an INSERT statement:

Syntax

WITH cte_name AS (
    -- Define the CTE here
    SELECT columns
    FROM source_table
    WHERE conditions
)
INSERT INTO target_table (columns)
SELECT columns
FROM cte_name;

Example

Suppose you have the following tables:

1. Source Table: employees
   

   id	name	department_id	salary
   1	John	101	50000
   2	Alice	102	60000
   3	Bob	103	45000

2. Target Table: high_salary_employees
   

   id	name	salary

Using a WITH Clause

Insert employees with a salary greater than 50,000 into the high_salary_employees table:

WITH high_earners AS (
    SELECT id, name, salary
    FROM employees
    WHERE salary > 50000
)
INSERT INTO high_salary_employees (id, name, salary)
SELECT id, name, salary
FROM high_earners;

Explanation:

1. The WITH clause (high_earners) creates a temporary result set of employees with a salary greater than 50,000.
2. The INSERT INTO statement takes the result of the CTE and inserts it into the high_salary_employees table.

Output

After running the query, the high_salary_employees table will contain:

Advantages of Using WITH Clause

1. Readability: Simplifies complex queries by breaking them into smaller, more manageable parts.
2. Reusability: The CTE can be referenced multiple times within the query.
3. Debugging: Makes it easier to debug and test parts of the query.

Additional Example: Using Joins

If you need to insert data into a table after joining multiple tables, the WITH clause simplifies the process:

WITH department_totals AS (
    SELECT department_id, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department_id
)
INSERT INTO department_summary (department_id, total_salary)
SELECT department_id, total_salary
FROM department_totals
WHERE total_salary > 100000;

Summary

The WITH clause is a powerful tool for creating temporary result sets, and it works seamlessly with INSERT statements. It improves the readability and modularity of SQL queries, making them easier to understand and maintain.