15.00 - Upsert Task - MultiLoad

Teradata MultiLoad Reference

prodname
MultiLoad
vrm_release
15.00
category
Programming Reference
featnum
B035-2409-034K

Upsert Task

               /******************************************************************/
               /*   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    */