Merge into a temporal table

SQL Server 2016 supports Temporal Tables (aka Type 2 or Historical Integrity). Unfortunately, in this first release you cannot use a change datetime value from the source table. It uses the system time when the update occurs. So, if you want to use your source system change datetimes, you won't be able to use Temporal Tables. Fortunately, the Merge statement is well suited.

Here is a sample Merge statement for a self managed Temporal Table.

Note, the Transaction and TABLOCKX is optional. I like using the table lock when there is a possibility of lock escalation. Two concurrent lock escalations are a guaranteed way of a deadlock. The TABLOCKX will ensure the Merge does not start until it can get a full table lock, so no chance of deadlock. This is at the cost of a little concurrency.

This statement has two insert statements. The first insert is for modified records (it's inserting the newer record) and the second insert is for new (not matched) records.

Please see this statement as a sample. You may want to change logic for EffectiveFromDate if you know the "real" time this record was active (CreatedDate, for example).

BEGIN TRANSACTION

     SELECT TOP 0 * from MySchema.MyTable WITH (TABLOCKX)

     INSERT INTO MySchema.MyTable(IsCurrent

                                                       ,MyKey1

                                                       ,MyKey2

                                                       ,EffectiveFromDate

                                                       ,EffectiveToDate

                                                       ,MyAttribute1

                                                       ,MyAttribute2

                                                       ,DatetimeChanged)

              SELECT 1 IsCurrent

                           ,MyKey1

                           ,MyKey2

                           ,DatetimeChanged EffectiveFromDate

                           ,'9999-12-31' EffectiveToDate

                           ,MyAttribute1

                           ,MyAttribute2

                           ,DatetimeChanged

            FROM

                (MERGE MySchema.MyTable Dst

                    USING LifeSTG.MySchema.MyTable Src

                            ON Dst.MyKey1=Src.MyKey1

                            AND Dst.MyKey2=Src.MyKey2

                            AND Dst.IsCurrent=1

                    WHEN NOT MATCHED

                            THEN--record from source doesn't exist in Destination

                                --INSERT the record into Destination

                                INSERT (IsCurrent

                                                                     ,MyKey1

                                                                     ,MyKey2

                                                                     ,EffectiveFromDate

                                                                     ,EffectiveToDate

                                                                     ,MyAttribute1

                                                                     ,MyAttribute2

                                                                     ,DatetimeChanged)

                                        VALUES (1

                                                                                  ,MyKey1

                                                                                  ,MyKey2

                                                                                  ,'1800-01-01'

                                                                                  ,'9999-12-31'

                                                                                  ,MyAttribute1

                                                                                  ,MyAttribute2

                                                                                  ,DatetimeChanged)

                    WHEN MATCHED --record from source exists in Destination,so check if any of the attributes have changed

                                --AND Dst.DatetimeChanged <> Src.DatetimeChanged --using ChangedDateTime checks on other columns not required

                                AND CHECKSUM(Dst.MyAttribute1

                                                                           ,Dst.MyAttribute2

                                                                           ,DatetimeChanged)

                                    <> CHECKSUM(Src.MyAttribute1

                                                                           ,Src.MyAttribute2

                                                                           ,DatetimeChanged)

                            THEN --Update the matching record as no longer current because the attributes have changed

                                UPDATE

                                        SET Dst.IsCurrent=0

                                            ,Dst.EffectiveToDate=Src.DatetimeChanged

                    OUTPUT   Src.MyKey1

                            ,Src.MyKey2

                            ,Src.MyAttribute1

                            ,Src.MyAttribute2

                            ,Src.DatetimeChanged

                            ,$Action as MergeAction

                ) MRG

    WHERE MRG.MergeAction='UPDATE'

COMMIT TRAN