Atomic UPSERT - ODBC Driver for Teradata

ODBC Driver for Teradata® User Guide

Product
ODBC Driver for Teradata
Release Number
16.20
Published
August 2020
Language
English (United States)
Last Update
2020-08-25
dita:mapPath
fxv1527114222338.ditamap
dita:ditavalPath
Audience_PDF_product_legacy_odbc_include.ditaval
dita:id
B035-2526
lifecycle
previous
Product Category
Teradata Tools and Utilities

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").