Examples: MERGE non-PTI Table with a PTI Time-Bucket Table - Analytics Database - Teradata Vantage

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

CREATE TABLE mrg_tgt_ns(c1 INT, c2 INT)
PRIMARY TIME INDEX(TIMESTAMP(1), DATE '2016-10-15',
HOURS(1));

Below is the table definition for the non-PTI table mrg_src_r.

CREATE TABLE mrg_src_r
(c0 TIMESTAMP(1), c1 INT, c2 INT);

This example merges the non-PTI source table mrg_src_r into the nonsequenced PTI target table mrg_tgt_ns.

MERGE INTO mrg_tgt_ns AS tgt
USING mrg_src_r AS src
ON src.c0 = tgt.TD_TIMECODE
WHEN MATCHED THEN UPDATE SET c2 = 70
WHEN NOT MATCHED THEN INSERT (src.c0, src.c1, src.c2);

This example merges the non-PTI source table mrg_src_r into the nonsequenced PTI target table mrg_tgt_ns with an ON clause equality condition for the target table TD_TIMECODE column.

MERGE INTO mrg_tgt_ns AS tgt
USING mrg_src_r AS src
ON tgt.TD_TIMECODE = TIMESTAMP '2016-10-16 10:32:12.2'
WHEN MATCHED THEN UPDATE SET c2 = 72
WHEN NOT MATCHED THEN INSERT (TIMESTAMP '2016-10-16 10:32:12.2', src.c1, src.c2);