Home Forums SQL Server 2005 Business Intelligence Best Practice for loading DW tables from live source with timestamp/Rowversion column to identify changes RE: Best Practice for loading DW tables from live source with timestamp/Rowversion column to identify changes

  • sotn (5/3/2013)


    thanks for the reply.

    Unfortunately, I've already examined that option but we cannot really change our erp system easily, so I hoping for an easy 'timestamp' comparison rather than comparing all fields.

    You should be able to do the same thing with the rowversion column. Create a log table for the rowversion value at the time you start an extraction. At the start of the extraction, capture the current rowversion value into a variable (DECLARE @currentRowversionValue varbinary(8) = @@DBTS) and the maximum rowversion value from the log table into a variable (DECLARE @maxLoggedRowversion varbinary(8) = (SELECT MAX(rowversionValue) FROM extractLog)). Within the extraction process, extract only rows where rowversion > @maxLoggedRowversion. When that's complete, INSERT INTO extractLog(rowversionValue) SELECT @currentRowversionValue. You may want to wrap all of that into an explicit transaction with appropriate error handling so that the extractLog table is only updated when the extraction process completes without error.

    You can index your rowversion column to make this faster. I would create a non-clustered index with only the rowversion column (with an appropriate maintenance plan to manage fragmentation). Presumably you'll want all columns of an updated row in the extract, so an index seek on the rowversion index to identify the updated rows followed by a key lookup to the underlying table to get all the columns will not be unduly burdensome.

    Jason Wolfkill