16.20 - Atomic UPSERT - ODBC Driver for Teradata

ODBC Driver for Teradata® User Guide

ODBC Driver for Teradata
August 2020
User Guide

UPSERT is a composite of UPDATE and INSERT operations, applied to a single row in a table, so that, if the UPDATE fails because the target row does not exist, the INSERT is automatically executed.

An UPSERT-like feature was formerly supported by load utilities such as Teradata TPump and Teradata MultiLoad, but the implementation was a two-pass one (first UPDATE will be executed; if it fails, then INSERT will be executed). For better performance of the UPSERT-like queries, the Atomic UPSERT feature was implemented on the server.

To support UPSERT SQL for Teradata Database, ODBC Driver for Teradata was enhanced to support the feature in the Teradata Tools and Utilities. UPSERT allows all client applications to use this UPSERT functionality as a single query (or one-pass strategy), which has better performance when compared to Teradata TPump and Teradata MultiLoad batch logic.

While establishing a connection to Teradata Database, ODBC Driver for Teradata determines whether the UPSERT feature is supported by Teradata Database. If the database does not support the UPSERT feature, any UPSERT statements submitted by the application return the following general error to the application:

("UPDATE..ELSE..INSERT (UPSERT) is not supported by Database").