Example: Merging Nontemporal Table Data into a Row-Partitioned Bitemporal Table - Analytics Database - Teradata Vantage

Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
eud1628112402879.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
fif1472244754791
lifecycle
latest
Product Category
Teradata Vantage™

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