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
Referring to other tables
INSERT, UPDATE and DELETE with JOIN
10. Insert rows using JOIN
INSERT, UPDATE, and DELETE with subqueries
Summary

Instruction

When writing an INSERT statement, you can also refer to other tables by using a JOIN in a SELECT clause.

The store staff would like to add new tables that help to prepare detailed sales reports. They have created a table named SalesReport that combines details about individual purchases: PurchaseId, TotalPrice, OrderDate, and Quantity. Additionaly, the table also includes details about the product bought in this purchase: ProductName from Product. To insert data into this table, you may use the following statement:

INSERT INTO SalesReport
SELECT
  pu.Id AS PurchaseId,
  pr.Name AS ProductName,
  pu.TotalPrice,
  pu.OrderDate,
  pu.Quantity 
FROM Purchase pu
INNER JOIN Product pr 
ON pu.ProductId = pr.Id
WHERE IsCompleted = 1
AND pu.OrderDate <= '2016-12-31';

The SELECT query chooses important product details for each purchase, making it easy to create sales reports. The query joins data from the Product and Purchase tables and puts it into the SalesReport table.

Exercise

Insert the sales information for 2017 into the SalesReport table. Insert the following data:

  • ID of the purchase (as PurchaseId).
  • The name of the product (as ProductName).
  • The total price for this purchase.
  • The order date.
  • The quantity.

Stuck? Here's a hint!

You may use YEAR(Purchase.OrderDate) to extract the year from the date.