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.
