Introduction
INSERT with SELECT
7. Complex SELECT with INSERT
Referring to other tables
INSERT, UPDATE and DELETE with JOIN
INSERT, UPDATE, and DELETE with subqueries
Summary

Instruction

Great! The SELECT query used with an INSERT can be as complicated as it needs to be. For example, we could add a WHERE clause to filter records. Check out the following query:

INSERT INTO ProductHistory(Id, Name, Price)
SELECT Id, Name, Price
FROM Product
WHERE YEAR(DeliveredDateTime) = 2017;


This chooses the ID, name, and price of products that were delivered sometime in 2017. All these rows are inserted into the report table ProductHistory.

The SELECT query in an INSERT can be complicated. For example, it can contain an aggregate function and GROUP BY to compute some statistics to be used in reporting later on.

Exercise

Look! There are new orders in the Purchase table. The manager would like to add to the PurchaseHistory table all purchases from January 2019.

Stuck? Here's a hint!

You need

YEAR(OrderDate) = 2019 AND MONTH(OrderDate) = 1
in the WHERE clause.