Wednesday, November 30, 2016
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).
SELECT TOP 0 * from MySchema.MyTable WITH (TABLOCKX)
INSERT INTO MySchema.MyTable(IsCurrent
SELECT 1 IsCurrent
(MERGE MySchema.MyTable Dst
USING LifeSTG.MySchema.MyTable Src
WHEN NOT MATCHED
THEN--record from source doesn't exist in Destination
--INSERT the record into Destination
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
THEN --Update the matching record as no longer current because the attributes have changed
,$Action as MergeAction