Slowly Changing Dimensions - SQL Server Integration Services Design Patterns - page 360

Figure 13-7 . Control flow for the Merge pattern
Handling Type 1 Changes
Listing 13-1 shows the first MERGE statement we'll run to update all of our Type 1
columns in the destination table. The ON ( ) section specifies the keys that we'll be
matching on (in this case, the business key for the table). In the WHEN MATCHED sec-
tion, we include DEST.EndDate is NULL to ensure that we are only updating the
current record (this is optional—in many cases, you do want to update all records and
not just the current one). The THEN UPDATE section contains the list of our Type 1
columns that we want to update.
Listing 13-1 . MERGE Statement for Type 1 Columns
MERGE INTO [DimProduct] AS DEST
USING [Staging] AS SRC
ON (
DEST.ProductAlternateKey = SRC.ProductAlternateKey
)
WHEN MATCHED AND DEST.EndDate is NULL -- update the
current record
THEN UPDATE SET