4 Replies Latest reply: Apr 27, 2016 11:47 AM by Riley MacDonald RSS

    Overwriting Rows and Incremental Load

    Riley MacDonald



      I have the following table which will be refreshed daily and will contain years of daily data which means ~30 million records:





      I want to load all of the historical data from my database into a QVD and then everyday take the last 3 days of data from my database and add any new records (which I can determine by the Key field) and then if a record comes in during the last 3 days load that already has an existing key in the historical QVD I would like to overwrite that record. I need to do this as the data in the column Qty can change historically over the last 3 days. Can anyone help with this?




        • Re: Overwriting Rows and Incremental Load
          Nicole Smith

          Here is some "pseudo" code to hopefully get you started...


          /* Check if QVD exists, if not load all history from DB */

          IF FileTime(YourQVD.qvd)>0 THEN

               /* Load last 3 days from DB */

               LET vMinDate = date(today()-3);


               SELECT *

               FROM YourDatabaseTable

               WHERE YourDateField >= $(vMinDate);


               /* Load only things not updated from QVD */

               CONCATENATE (YourTable)

               LOAD *

               FROM YourQVD.qvd

               WHERE not exists(YourKeyField);



               /* Load everything from database */


               SELECT *

               FROM YourDatabaseTable;



          STORE * FROM YourTable INTO YourQVD.qvd (qvd);

          • Re: Overwriting Rows and Incremental Load
            Riley MacDonald

            This works fine for my first case of only adding the new records, however how do I deal with records that need to be overwritten? In this case you could imagine my key of SKU&Day would exist in the stored QVD already but the Qty would be different and therefore I would need to take this record and add it into my stored QVD and ensure the old record is overwritten with the new one.