Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries in MS SQL Server

Introduction
CTEs with data modifying statements
7. CTEs with INSERT statement 1
Summary

Instruction

OK. Now let's begin to tackle CTEs with INSERT. Look at the code below.

WITH Plus30 AS (
  SELECT
    Id,
    FirstName,
    LastName,
    City
  FROM Customer
  WHERE Age >= 30
)

INSERT INTO Customer30Plus
SELECT
  *
FROM Plus30;

In the WITH query, we select the Id, FirstName, LastName, and City of all customers who are over 30 years old.

In the outer query, the data returned by the CTE Plus30 is inserted into the Customer30Plus table. Note that the outer INSERT statement can refer to the CTE Plus30.

Exercise

We created another table called GroceriesCustomers. Fill it with the following information: Id, Name, and City of the grocery store. Only if any customers live in that city.

Stuck? Here's a hint!

In the CTE query, select data from the GroceryStore table. In the outer query, select all data from the CTE and insert it into our new GroceriesCustomers table.