Introduction
CTEs with data modifying statements
8. CTEs with INSERT statement 2
Summary

Instruction

Very good. Look at another example of a CTE with an INSERT statement.

WITH AvgPrices AS (
  SELECT
    GroceryStoreId,
    AVG(Price) AS AvgPrice
  FROM Product
  GROUP BY GroceryStoreId
)

INSERT INTO GroceryAvgPrices
SELECT
  *
FROM AvgPrices
WHERE GroceryStoreId != 2;

In this case, we created the CTE to retrieve the average price of products for each grocery store. We used the AVG aggregate function to compute the average price of all products in the grocery store. In this query, records are grouped according to GroceryStoreId, using the GROUP BY clause.

In the outer query, we inserted rows into the GroceryAvgPrices table, but only for grocery stores with IDs different than 2.

Exercise

We have created a new table called ProductMaxPrice. Let's insert the names of products and their highest prices into this table.

In the CTE, select the Name and max price for every product.

In the outer statement, insert the name of products and their highest prices into the ProductMaxPrice table, but only for products N'Bread' and N'Milk'.

Stuck? Here's a hint!

Select the name and maximum price for each product in a CTE. Then insert the name and maximum price from the CTE for the products 'Milk' and 'Bread' into the ProductMaxPrice table.