Introduction
CTEs with data modifying statements
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.
