1 Reply Latest reply on Jan 3, 2017 8:05 AM by Joshua Milligan

    Incremental Extract Update based on Row Id?

    sumesh behl

      Hi ,


      I want to set an Incremental Extract refresh. For this i need to specify a unique field on which the refresh would occur. However in my table there is no primary key.


      Can i add a new row id in the SQL code and specify it as a field for incremental refresh. Would this be right approach?



        • 1. Re: Incremental Extract Update based on Row Id?
          Joshua Milligan



          It depends on what you mean by adding a new row id using SQL code. 


          What the incremental refresh does is use the field you specify and looks for rows of data having values for that field that are greater than the values already existing in the extract.  There is no requirement that these values be unique (for example, you could have 100 rows all having a 1 value today and then a few more new rows with a 2 that would be added on the next incremental refresh -- or you could have 1,2...100 and then new rows with 101,102... that would be incrementally added) So, as long as your SQL code gave you values that were greater than what you had previously, those rows would get included in an incremental refresh.


          Be aware that any new rows that have the same or lesser values as those which already exist in the extract will not be added in an incremental refresh.  Also, incremental refresh is only good for adding rows, so it won't catch anything that was deleted or changed at the source.


          One thing you could do with SQL is to create a new column using the GETDATE() function.  As you select the new rows that you want to add in the incremental refresh, they will be tagged with a timestamp that is going to ever increase -- that might be a bit easier than trying to manually generate some kind of incrementing ID field (though having a unique row ID is useful for some kinds of analysis -- so it may be worth it).


          Hope that helps!


          2 of 2 people found this helpful