Part 5: Incremental Load - Using TSQL Merge Function

Continue with my Previous Posts:

Part 1: Incremental Load

Part 2: Incremental Load - Using Lookup Component and Hash Function

Part 3: Incremental Load - Using Lookup Component

Part 4: Incremental Load - Using MERGE JOIN

MERGE [dbo].[MyDestination] TT

USING (       SELECT [CustomerAlternateKey],[Title],[FirstName],[MiddleName],[LastName],[BirthDate],[MaritalStatus],[Suffix],[Gender],[EmailAddress],[YearlyIncome],[TotalChildren],[NumberChildrenAtHome],[EnglishEducation],[EnglishOccupation],[HouseOwnerFlag],[NumberCarsOwned]

                            ,HASHBYTES('SHA2_256',CONCAT([Title],[FirstName],[MiddleName],[LastName],[BirthDate],[MaritalStatus],[Suffix],[Gender],[EmailAddress],[YearlyIncome],[TotalChildren],[NumberChildrenAtHome],[EnglishEducation],[EnglishOccupation],[HouseOwnerFlag],[NumberCarsOwned])) DWH_Hash

              FROM [dbo].[DimCustomer]

         )ST

        ON (TT.[CustomerAlternateKey] = ST.[CustomerAlternateKey])

--Inserts

WHEN NOT MATCHED BY TARGET THEN

        INSERT ([CustomerAlternateKey],[Title],[FirstName],[MiddleName],[LastName],[BirthDate],[MaritalStatus],[Suffix],[Gender],[EmailAddress],[YearlyIncome],[TotalChildren],[NumberChildrenAtHome],[EnglishEducation],[EnglishOccupation],[HouseOwnerFlag],[NumberCarsOwned])

        VALUES (ST.[CustomerAlternateKey],ST.[Title],ST.[FirstName],ST.[MiddleName],ST.[LastName],ST.[BirthDate],ST.[MaritalStatus], ST.[Suffix], ST.[Gender], ST.[EmailAddress], ST.[YearlyIncome], ST.[TotalChildren], ST.[NumberChildrenAtHome], ST.[EnglishEducation], ST.[EnglishOccupation], ST.[HouseOwnerFlag], ST.[NumberCarsOwned])

--Updates

WHEN MATCHED AND (TT.[DWH_Hash] != ST.[DWH_Hash]) THEN

        UPDATE SET BirthDate = ST.BirthDate

                     , EmailAddress = ST.EmailAddress

                     , EnglishEducation = ST.EnglishEducation

                     , EnglishOccupation = ST.EnglishOccupation

                     , FirstName = ST.FirstName

                     , Gender = ST.Gender

                     , HouseOwnerFlag =ST.HouseOwnerFlag

                     , LastName = ST.LastName

                     , MaritalStatus = ST.MaritalStatus

                     , MiddleName = ST.MiddleName

                     , NumberCarsOwned = ST.NumberCarsOwned

                     , NumberChildrenAtHome = ST.NumberChildrenAtHome

                     , Suffix = ST.Suffix

                     , Title = ST.Title

                     , TotalChildren = ST.TotalChildren

                     , YearlyIncome = ST.YearlyIncome

                     , [DWH_Hash] = ST.[DWH_Hash]

Continue with my Next Posts:

Part 6: Incremental Load - Using Lookup Component with Customized SCD (Slowly Changing Dimension) Type 2