Example: Merging Nontemporal Table Data into a Row-Partitioned Bitemporal Table - Advanced SQL Engine - Teradata Database

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)
   );