1 Reply Latest reply: Nov 12, 2014 7:42 AM by Gysbert Wassenaar RSS

    Insert, Update, Delete Incremental Load Without Entire History

      So basically we get a nightly feed which includes 7 days worth of updated/new data. Along with that we get a file including all deleted id's. My objective is to do an incremental load, insert and update, using my initial QVD (made form a "catch up" file) and the 7 day file we get nightly. I then need to use the deleted ID file to remove any records from the table I created in the incremental load. Since we don't get a full export I can't utilize the inner load of all IDs most people use.

       

      What would be the best way to go about this?

        • Re: Insert, Update, Delete Incremental Load Without Entire History
          Gysbert Wassenaar

          Maybe like this:

           

          // load deleted ID's in a temporary table
          T1:
          LOAD ID FROM deleted_ids_file (txt, ...etc...) ;
          
          // load only the records from the incremental_data qvd that
          // are not in the deleted id's file in a temporary table
          T2:
          LOAD * FROM incremental_data.qvd (qvd)
          WHERE NOT EXISTS(ID);
          
          // store the records from the remaining ID's
          STORE T2 into incremental_data.qvd (qvd);
          
          // drop tables that are no longer needed
          DROP TABLES T1, T2;
          
          // load the updated and new records from the nightly 7 day file
          Final:
          LOAD * FROM sevendaysupdates.qvd (qvd);
          
          // load only the records from the incremental_data qvd that are
          // not already in the nightly 7 day file
          concatenate(Final)
          LOAD * FROM incremental_data.qvd (qvd)
          WHERE NOT EXISTS(ID);
          
          // store the complete set of records in the incremental qvd
          STORE T2 into incremental_data.qvd (qvd);