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.
