Multiload UPSERT in Teradata with Example

UPSERT is an operation in DBMS, where if the new Delta contains updates for existing rows, they are Updated first. The remaining new rows in the Delta file will be inserted.

Multiload is well-equipped to handle such requests.

The following example illustrates how you can use Multiload to perform an UPSERT operation:

Note: This is different from the SQL UPSERT (UPDATE ... ELSE INSERT...)

Sample script:

.LOGTABLE WORK_TBLS.USER_INTERACT_ID_LOG; .LOGON DWTEST1/USER1,PWD1; .BEGIN IMPORT MLOAD TABLES DW_TBLS.USER_INTERACT_ID WORKTABLES WORK_TBLS.USER_INTERACT_ID_WK ERRORTABLES WORK_TBLS.USER_INTERACT_ID_ET WORK_TBLS.USER_INTERACT_ID_UV; .LAYOUT DATAIN_LAYOUT; .FIELD INTERACT_DESC 1 VARCHAR(30); .FIELD INTERACT_TYP * varchar(10); .DML LABEL UPDATE_DML DO INSERT FOR MISSING UPDATE ROWS; UPDATE DW_TBLS.USER_INTERACT_ID SET INTERACT_DESC = :INTERACT_DESC WHERE INTERACT_TYP = :INTERACT_TYP ; INSERT INTO DW_TBLS.USER_INTERACT_ID ( INTERACT_TYP ,INTERACT_DESC ) values( :INTERACT_TYP ,:INTERACT_DESC ); .IMPORT INFILE /tmp/InteractionTypes.csv.tmp1 FORMAT vartext ',' LAYOUT DATAIN_LAYOUT APPLY UPDATE_DML ; .END MLOAD; .LOGOFF; DO INSERT FOR MISSING UPDATE ROWS - This keyword gives a HINT to multiload that an UPSERT is to be performed. Otherwise, all rows will qualify for both UPDATE and INSERT, and the UPDATE rows go to _UV table. Keep Reading to understand how to run a multiload, restart etc.!! Subscribe with us if you like our Blog. Help us understand what topics should we cover.