Wednesday, November 30, 2016

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


No comments: