You can use the temporal form of the MERGE statement to merge data from a nontemporal table into a primary-indexed temporal table. Suppose you have the following nontemporal table called Policy_Changes:
CREATE TABLE Policy_Changes( Policy_ID INTEGER, Customer_ID INTEGER, Policy_Type CHAR(2) NOT NULL, Policy_Details CHAR(40) );
Suppose you also have the following bitemporal table called Policy that is row partitioned according to the partitioning guidelines for a bitemporal table:
CREATE MULTISET TABLE Policy( Policy_ID INTEGER, Customer_ID INTEGER, Policy_Type CHAR(2) NOT NULL, Policy_Details CHAR(40), Validity PERIOD(DATE) AS VALIDTIME, Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME ) PRIMARY INDEX(Policy_ID) PARTITION BY CASE_N((END(Validity) IS NULL OR END(Validity) >= CURRENT_DATE AT '-12:59') AND END(Policy_Duration) >= CURRENT_TIMESTAMP, END(Validity) < CURRENT_DATE AT '-12:59' AND END(Policy_Duration) >= CURRENT_TIMESTAMP, END(Policy_Duration) < CURRENT_TIMESTAMP);
The following statement performs a sequenced merge in the valid-time dimension into the Policy table from the Policy_Changes table where the period of applicability is December 1, 2009 to December 7, 2009.
The matching condition is applied on open rows of the Policy table where the period of validity overlaps the period of applicability. If the matching condition is satisfied, a sequenced update is performed; if the matching condition is not satisfied, a sequenced insert is performed.
SEQUENCED VALIDTIME MERGE INTO Policy USING ( NONSEQUENCED VALIDTIME PERIOD (DATE'2009-12-01', DATE'2009-12-07') SELECT source.Policy_ID, source.Customer_ID, source.Policy_Type, source.Policy_Details, target.Validity AS vt, END(target.Policy_Duration) AS ett FROM Policy_Changes source LEFT OUTER JOIN Policy target ON source.Policy_ID = target.Policy_ID WHERE (vt IS NULL OR ((BEGIN(vt) < DATE '2009-12-07') AND (END(vt) > DATE '2009-12-01') AND (ett = TIMESTAMP '9999-12-31 23:59:59.999999')) ) ) AS merge_source ( PID, CID, PType, PDetails, j, k ) ON (Policy_ID = merge_source.PID) AND END(Validity) = END(j) AND END(Policy_Duration) = k WHEN MATCHED THEN UPDATE SET Policy_Details = merge_source.PDetails WHEN NOT MATCHED THEN INSERT ( merge_source.PID, merge_source.CID, merge_source.PType, merge_source.PDetails, PERIOD(TEMPORAL_DATE, UNTIL_CHANGED) );