The two demo tables are empty at the moment. You add data to them with the INSERT statement and you can specify the data values in the VALUES clause. You can insert more than one row in a single statement, as the following code shows, by inserting two rows into the dbo.SimpleOrderDetails table in a single statement. You can omit the column names in the INSERT part. However, this is not a good practice. Your insert depends on the order of the columns, if you don't specify the column names explicitly. Imagine what could happen if somebody later changes the structure of the table. In a bad outcome, the insert would fail. However, you would at least have the information that something went wrong. In a worse outcome, the insert into the altered table could even succeed. However, now you can finish with wrong data in wrong columns, without even noticing this problem, like the following code example shows:
INSERT INTO dbo.SimpleOrders (OrderId, OrderDate, Customer) VALUES (1, '20160701', N'CustA'); INSERT INTO dbo.SimpleOrderDetails (OrderId, ProductId, Quantity) VALUES (1, 7, 100), (1, 3, 200);
The following query checks the recently inserted data. As you probably expected, it returns two rows:
SELECT o.OrderId, o.OrderDate, o.Customer, od.ProductId, od.Quantity FROM dbo.SimpleOrderDetails AS od INNER JOIN dbo.SimpleOrders AS o ON od.OrderId = o.OrderId ORDER BY o.OrderId, od.ProductId;
Here is the result:
OrderId OrderDate Customer ProductId Quantity ----------- ---------- -------- ----------- ----------- 1 2016-07-01 CustA 3 200 1 2016-07-01 CustA 7 100
The next example shows how to update a row. It updates the Quantity column in the dbo.SimpleOrderDetails table for the order with OrderId equal to 1 and for the product with ProductId equal to 3:
UPDATE dbo.SimpleOrderDetails SET Quantity = 150 WHERE OrderId = 1 AND ProductId = 3;
You can use the same SELECT statement to check the data—whether it is updated correctly, as introduced right after the inserts.
You really need to check the data often, right after a modification. For example, you might use the IDENTITY property or the SEQUENCE object to generate the identification numbers automatically. When you insert an order, you need to check the generated value of the OrderId column, to insert the correct value to the order details table. You can use the OUTPUT clause for this task, as the following code shows:
INSERT INTO dbo.SimpleOrders (OrderId, OrderDate, Customer) OUTPUT inserted.* VALUES (2, '20160701', N'CustB'); INSERT INTO dbo.SimpleOrderDetails (OrderId, ProductId, Quantity) OUTPUT inserted.* VALUES (2, 4, 200);
The output of the two inserts is shown as follows:
OrderId OrderDate Customer ----------- ---------- -------- 2 2016-07-01 CustB OrderId ProductId Quantity ----------- ----------- ----------- 2 4 200