The Atomic Upsert Feature
The basic upsert function has been enhanced to support an Atomic upsert capability. This enhancement permits Teradata TPump to perform single‑row upserts in a single pass. This one‑pass logic adopts the upsert‑handling technique used by MultiLoad. The one‑pass logic is designated Atomic to distinguish the grouping of paired UPDATE and INSERT statements which are executed as a single SQL statement.
The syntax for Atomic upsert consists of an UPDATE statement and an INSERT statement, separated by an ELSE keyword.
Existing Teradata TPump scripts using the Atomic upsert form do not have to be changed. Teradata TPump will automatically convert the old UPDATE/INSERT pairs to the Atomic upsert feature whenever appropriate. Any attempts to change this will result in a syntax error.
The new syntax, which can also be used by CLIv2 and BTEQ applications, is dependent on whether or not the database version, against which the Teradata TPump job is run, supports this feature. If the database does not support Atomic Upsert, Teradata TPump reverts to the earlier logic of sending the INSERT request if an UPDATE request fails.
The three basic constraints on the upsert feature are:
Although Teradata TPump does not verify basic upsert constraints, Teradata Database will reject Atomic upsert constructs that fail the constraint test, and notify Teradata TPump by returning an appropriate error message to the client.
If the Atomic Upsert is done on a Temporal table, the optional Temporal qualifier can only be added before the UPDATE statement.