Follow the easy steps below to create your environment and work area.

1.    Open Microsoft SQL Server Management Studio.

2.    Right click the database DbWork and choose query window.

3.    Use the listing below to create an order table that would contain all the orders by a specific customer id.

Listing 1 - T-SQL to create the order table

CREATE TABLE [dbo].[TblOrder](
      [OrderId] [bigint] IDENTITY(1,1) NOT NULL,
      [CustomerId] [int] NOT NULL,
      [OrderTotal] [decimal](18, 0) NOT NULL
) ON [PRIMARY]

4.    Use the following listing in order to insert some records in the order tables.

Listing 2 - T-SQL to insert data into the order table using the row constructor method

Insert into TblOrder (CustomerId, OrderTotal)
Values      (1,1000),
            (2,1500),
            (6,10000)

5.    We now need to add a column to the customer table and initialize the value to zero. Use the listing below.

Listing 3 - T-SQL to alter the customer table to add the customer total column

Alter Table TblCustomer
Add CustomerTotal Decimal Default 0;
  
Update TblCustomer
Set CustomerTotal = 0;

6.    In the listing of step 4 above you can see that I did make two orders for an existing two customers and added one order for a customer id 10 (does not exist in the customer table).

7.    Suppose your manager came in and said we have discrepancies in our database. We would like to have for each customer id to have customer total and every customer id found in the order table should have dummy customer information in order to have an integral database.

8.    How to solve the problem? Well SQL Server 2008 provided the merge statement to do this. The Merge statement allows us to Insert, Update or Delete on target table based on the results of a join with a source table (Wait for the example).

9.    Our target table here would be the customer table. Our source table would be the order table.

10. I will post the listing and then explain it.

Listing 4 - T-SQL Merge statement example

  Merge TblCustomer
  Using ( Select CustomerId, OrderTotal From TblOrder ) As OrderSrc (CustId, Amount)
  On TblCustomer.CustomerId = OrderSrc.CustId
  When Matched Then
      Update Set CustomerTotal = CustomerTotal + OrderSrc.Amount
  When Target Not Matched Then
      Insert (CustomerName, CustomerSince, CustomerArea, CustomerTotal)
      values ('New',Getdate(), 'New', OrderSrc.Amount)
  When Source Not Matched Then
      Update Set CustomerTotal = 0;

11. The first line contains the target table (TblCustomer).

12. The second contains the source table selection where I have a select statement from the TblOrder renamed as OrderSrc.

13. When the two tables have something in common, the order amount is added to the customer total.

14. When there is a record in the Order and no record inside the customer table, a new customer is created automatically with the amount.

15. When there is no record in order table, the customer total in the source table is initialized to zero.