Upsert Task - MultiLoad

Teradata® MultiLoad Reference

Product
MultiLoad
Release Number
16.20
Published
October 2018
Language
English (United States)
Last Update
2018-10-10
dita:mapPath
lsl1527114222348.ditamap
dita:ditavalPath
Audience_PDF_product_tpt_userguide_include.ditaval
dita:id
B035-2409
lifecycle
previous
Product Category
Teradata Tools and Utilities
/******************************************************************/
/*   This task will change the phone numbers of all employees     */
/*   by first trying to update each row of the employee table,    */
/*   and then inserting the data if the update fails              */
/******************************************************************/
.Logtable Logtable003;            /*  Logon to Teradata Database &*/
.Logon tdpx/user,pwd;             /*  identify the restart table  */
.Begin Import Mload               /*  Specifyimport task and      */
      Tables Employee;            /*  identify target table       */
.Layout Layoutname;               /*  Provide layout name and     */
.Field EmpNum 1 integer;          /*  define fields of            */
.Field Fone * (char (10));        /*  the client record.          */
.DML Label DMLlabelname           /*  This DML request            */
Do insert for missing update rows;/*  calls for an upsert         */
Update Employee                   /*  Try an update statement to  */
 set PhoneNo = :Fone              /*  change the phone number     */
 where EmpNo = :EmpNum;           /*  for each employee.          */
Insert Employee                   /*  Complete the upsert         */
 (EmpNo, PhoneNo)                 /*  with the insert part        */
 values                           /*  for when the update         */
(:EmpNum, :Fone);                 /*  fails.                      */
.Import Infile INPUT              /*  Identify import file.       */
   Layout LayoutName
   Apply DMLlabelname;
.End Mload;                       /*  Initiate MLOAD processing.  */
.Logoff;                          /*  Logoff Teradata Database    */