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:
Post a Comment