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 the 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 the database, ODBC Driver for Teradata determines whether the UPSERT feature is supported by the 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").