1 Reply Latest reply: Aug 21, 2015 9:37 AM by Stefan Wühl RSS

    Load switch from incremental insert to update

    Nick Listerman

      Current script below.  This works great for finding newly created incidents from the last load but now I want to update my QVD incidents where sys_updated_on has been updated.

       

      I think I need to load the old data, query for new incidents where sys_updated_on > max(sys_updated_on), and then load old where not in new?  This is what I don't know how to accomplish with ODBC SELECT vs LOAD.

       

      tbl_incidents:

      LOAD inc_number,

          enduser,

           agent,

           opened_at,

           SDCI,

           short_description,

        sys_created_on,

           sys_updated_on

      FROM [incidents.qvd] (qvd);

       

      MaxDate:

      LOAD max(sys_created_on) as MaxDate

      Resident tbl_incidents;

       

      LET vMaxDate = chr(39) & TimeStamp(PEEK('MaxDate', 0, 'MaxDate')) & chr(39);

      DROP TABLE MaxDate;

       

      Concatenate select t.number_ as inc_number,

        t.u_requested_for as enduser,

          t.opened_by as agent,

          t.opened_at,

          sdci.name as SDCI,

          t.short_description,

          t.sys_created_on,

          t.sys_updated_on

       

      from t_incident t

      left join t_sdci sdci on sdci.id= t.sdci

       

      where t.sys_created_on >= $(vMaxDate);

         

      store tbl_incidents into 'incidents.qvd'(qvd);