OK. Now let's begin to tackle CTEs with INSERT. Look at the code below.
WITH plus30 AS (
SELECT
id,
first_name,
last_name,
city
FROM customer
WHERE age >= 30
)
INSERT INTO customer_30plus
SELECT
*
FROM plus30;
In the WITH query we select the id, first_name, last_name, and city of customers who are over 30 years old.
In the outer query, the data returned by the CTE plus30 are inserted into table customer_30plus. Note that the outer INSERT statement can refer to the CTE plus30.
We created another table called groceries_customers. Fill it with the following: id, name, and city of grocery store, only if any customers live in that city.
In the CTE query, select data from grocery_store table. In the outer query, select all data from the CTE, and insert them into our new groceries_customers table.
