UPSERT - Teradata

Compatibility: Teradata Extension

In V2R4.1, NCR is incorporating a new syntax for an atomic UPSERT command into Teradata. It is targeted for use with a CLIv2, program, like BTEQ or other SQL interfaces (ODBC, Queryman, JDBC, etc):

An UPSERT is the combination of an UPDATE and an INSERT. It works by monitoring the UPDATE. If it fails because the row it is supposed to change is missing, the system automatically does an INSERT of the row.

The syntax for the new UPSERT command:

The primary reason for this functionality is to support TPump. TPump takes advantage of this new command to more efficiently implement its UPSERT capability. Both TPump and MultiLoad have an internal UPSERT capability. However, an UPSERT requires the UPDATE statement to fail in order for the INSERT to be performed. In MultiLoad, every part of the UPSERT processing is executed within the Teradata database while working with entire blocks of data rows. Conversely, TPump does its work at the row level, not the block level.

This means, using TPump for an UPSERT to a Teradata database prior to V2R4.1, that when the UPDATE statement fails the database sends a status back to TPump on the originating host (mainframe or other computer) where it is executing. Then, TPump must package up the data for the INSERT and send it back to Teradata. This is rather slow and requires additional processing on the host computer. With V2R4.1, TPump submits a single command to perform the requested UPSERT.

The UPSERT syntax for TPump and Multiload will not be changed. Therefore, it is compatible with existing scripts and follows this format:

Considerations for using UPSERT:

  1. SAME TABLE: The UPDATE and INSERT specify the same table.
  2. SAME ROW: The UPDATE and INSERT specify the "same" row – the primary index value in the inserted row matches the one in the targeted update row.
  3. HASHED ROW ACCESS: The UPDATE fully specifies the primary index so that the targeted row can be accessed with a one-AMP hashed operation.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics