Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

How to Insert, Update, or Delete Data in MS SQL Server

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.