Examples: MERGE non-PTI Table with a PTI Time-Bucket Table - Advanced SQL Engine - Teradata Database

Time Series Tables and Operations

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
zzg1600277315070.ditamap
dita:ditavalPath
zzg1600277315070.ditaval
dita:id
B035-1208
lifecycle
previous
Product Category
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);