17.10 - Example: Merging into a Row-Partitioned Valid-time Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1182-171K
Language
English (United States)

The following example uses a nontemporal source table for the merge. Note that the values to be inserted can be any values, even those that would go into a different partition.

CREATE MULTISET TABLE bi_tgt_tbl
     (
      pkey_field INTEGER,
      int2_field INTEGER,
      vtcol PERIOD(DATE) NOT NULL AS VALIDTIME,
      ttcol PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL 
            AS TRANSACTIONTIME)
PRIMARY INDEX ( pkey_field )
PARTITION BY CASE_N(
((END(vtcol)) >= DATE ) AND ((END(ttcol)) >= CURRENT_TIMESTAMP(6)),
((END(vtcol)) <  DATE ) AND ((END(ttcol)) >= CURRENT_TIMESTAMP(6)),
(END(ttcol )) < CURRENT_TIMESTAMP(6));

CREATE SET TABLE src_tbl
     (
      pkey_field INTEGER,
      int2_field INTEGER)
PRIMARY INDEX ( pkey_field );

SEQUENCED VALIDTIME
MERGE INTO bi_Tgt_tbl
USING   /* This block prejoins and determines the target valid time
           values */
(
  NONSEQUENCED VALIDTIME PERIOD (DATE'2009-12-15', DATE'2009-12-18')
  AND CURRENT TRANSACTIONTIME
  SELECT  s.pkey_field, s.int2_field, END(b_t.vtcol) vtend,           END(b_t.ttcol) ttend
  FROM 	src_tbl s LEFT OUTER JOIN bi_tgt_tbl b_t
  ON s.pkey_field = b_t.pkey_field
) AS nonbi_srct ( pkey, int2, vtend, ttend)
ON (pkey_field = nonbi_srct.pkey) 
AND END(vtcol) = vtend 
AND END(ttcol) = ttend

WHEN MATCHED THEN
 UPDATE SET int2_field = nonbi_srct.int2
WHEN NOT MATCHED THEN
 INSERT ( nonbi_srct.pkey, nonbi_srct.int2, 
          PERIOD(TEMPORAL_DATE, UNTIL_CHANGED )
);