Common Table Expressions (CTEs) are a useful way to simplify complex queries in SQL Server (and other SQL databases). More often than not, we’ll see CTEs that are used by SELECT statements, but this isn’t necessarily required. CTEs can be followed by SELECT, INSERT, UPDATE, DELETE, or MERGE statements.

This article provides a simple example of using INSERT INTO with a CTE in SQL Server.

What is a Common Table Expression (CTE)?

A common table expression (CTE) is a temporary result set that we can reference within another statement. It’s similar to a derived table, but usually with improved readability and maintainability.

A CTE is particularly useful when:

• We need to break down a complex query into smaller, manageable parts.
• We want to use recursive queries.
• We wish to improve the readability of your SQL code.

The basic syntax for CTEs goes something like this:

WITH CTE_Name (column1, column2, ...) AS
(
    -- CTE query goes here
)
-- Consuming query goes here

So the CTE itself has a query, and then we follow that up with another query that uses the results of the CTE’s query.

Understanding the INSERT INTO Statement in SQL Server

The INSERT INTO statement (or simply INSERT statement) is used to insert new records into a table. It has two common forms:

1. Inserting specific values:

   INSERT INTO table_name (column1, column2, ...)
   VALUES (value1, value2, ...)

2. Inserting results from a query:

   INSERT INTO table_name (column1, column2, ...)
   SELECT column1, column2, ...
   FROM another_table;

We can use a CTE as the source of the data for the INSERT INTO statement, allowing us to pre-process or filter the data before inserting it into the target table. In this case, we would put the CTE before the INSERT INTO statement.

How to Use INSERT INTO with a CTE

When combining INSERT INTO with a CTE, the CTE allows us to build a complex query to retrieve or calculate data that can then be inserted into another table.

The basic syntax for combining INSERT INTO with a CTE looks something like this:

-- Define the CTE
WITH CTE_Name (column1, column2, ...) AS
(
    -- Complex query or logic goes here
    SELECT ...
    FROM ...
    WHERE ...
)

-- Insert the results of the CTE
INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM CTE_Name;

The key points here are:

• The CTE is defined using the WITH keyword, where we specify the CTE name and the query inside it.
• After defining the CTE, we perform an INSERT INTO operation, where the data comes from the result of the CTE.

Example: Using INSERT INTO with a CTE in SQL Server

Let’s walk through an example where we use a CTE to filter data from one table and then insert the filtered data into another table.

Scenario

We have two tables:

1. Employees – Contains employee details.
2. HighSalaryEmployees – A table where we want to insert the details of employees who earn more than $70,000.

Create the Tables

We’ll create the two tables using the following code:

-- Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Department NVARCHAR(100),
    Salary DECIMAL(10,2)
);

-- HighSalaryEmployees table
CREATE TABLE HighSalaryEmployees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Department NVARCHAR(100),
    Salary DECIMAL(10,2)
);

Insert Data into the Employees Table

We’ll insert data into the Employees table with the following code:

INSERT INTO Employees (EmployeeID, Name, Department, Salary) VALUES
(1, 'Andy', 'HR', 60000),
(2, 'Betty', 'Engineering', 85000),
(3, 'Chuck', 'Engineering', 90000),
(4, 'Dweezel', 'Marketing', 72000),
(5, 'Emily', 'Finance', 68000);

We haven’t inserted any data into the other table yet, because that’s what our CTE will be for.

Insert Data using the CTE

Now let’s use a CTE to insert data into the other table:

-- Define the CTE
WITH HighEarnersCTE (EmployeeID, Name, Department, Salary) AS
(
    SELECT EmployeeID, Name, Department, Salary
    FROM Employees
    WHERE Salary > 70000
)

-- Insert the results of the CTE
INSERT INTO HighSalaryEmployees (EmployeeID, Name, Department, Salary)
SELECT EmployeeID, Name, Department, Salary
FROM HighEarnersCTE;

So we create the CTE first, and then follow that up with the INSERT INTO statement.

Here’s an explanation of what each of those do:

• The CTE selects all employees who earn more than $70,000.
• The INSERT INTO statement inserts the results of the CTE (i.e., it inserts all employees earning more than $70,000 into the HighSalaryEmployees table).

Result

Let’s check the destination table’s contents:

SELECT * FROM HighSalaryEmployees;

Output:

EmployeeID  Name     Department   Salary
----------  -------  -----------  ------
2           Betty    Engineering  85000 
3           Chuck    Engineering  90000 
4           Dweezel  Marketing    72000 

As expected, it only contains the employees who earn more than 70,000.