Example: MERGE PTI Time-Bucket Table with a PTI No-Time-Bucket Table - Advanced SQL Engine - Teradata Database

Following are the table definitions for the examples.

Below is the definition for the sequenced PTI table src_s with a time zero specification of October 15, 2016, DATE '2016-10-15', and time bucket duration of 1 hour, HOURS(1).

CREATE TABLE src_s (
      TD_TIMEBUCKET BIGINT NOT NULL GENERATED SYSTEM TIMECOLUMN,
      TD_TIMECODE TIMESTAMP(1) NOT NULL GENERATED TIMECOLUMN,
      TD_SEQNO INT NOT NULL GENERATED TIMECOLUMN,
      c1 INTEGER,
      c2 INTEGER)
 PRIMARY TIME INDEX (TIMESTAMP(1), DATE '2016-10-15', HOURS(1), SEQUENCED(500));

This definition for the sequenced PTI table tgt_nb_s includes a time zero specification of October 15, 2016, DATE '2016-10-15', but does not have a time bucket duration.

CREATE TABLE tgt_nb_s (
      TD_TIMECODE TIMESTAMP(1) NOT NULL GENERATED TIMECOLUMN,
      TD_SEQNO INT NOT NULL GENERATED TIMECOLUMN,
      c1 INTEGER,
      c2 INTEGER)
 PRIMARY TIME INDEX (TIMESTAMP(1), DATE '2016-10-15', COLUMNS(c1), SEQUENCED(500));

The definition for this non-PTI table tgt_r2 includes columns named timecode and seqno, in addition to a primary index consisting of the column c1.

CREATE TABLE tgt_r2( 
   timecode TIMESTAMP(1),
   seqno INTEGER,
   c1 INTEGER,
   c2 INTEGER)
PRIMARY INDEX ( c1 );

In this example, the sequenced PTI table src_s with a time bucket is merged into sequenced PTI table tgt_nb_s, which does not have a time bucket.

MERGE INTO
tgt_nb_s AS tgt
USING (SELECT TD_TIMECODE, TD_SEQNO, c1, c2 FROM src_s)AS src
ON tgt.TD_TIMECODE = src.TD_TIMECODE AND
   tgt.TD_SEQNO = src.TD_SEQNO AND
   tgt.c1 = src.c1
WHEN MATCHED THEN UPDATE SET c2 = 70
WHEN NOT MATCHED THEN
INSERT (src.TD_TIMECODE, src.TD_SEQNO, src.c1, src.c2);