Only this week, get the SQL Complete Track of 9 courses in a special prize of $330 $89!
CTEs with data modifying statements
8. CTEs with INSERT statement 2


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

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

INSERT INTO GroceryAvgPrices
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.


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.