Using update filters: last modified date

Filter MF->SQL updates using the last modified timestamp; includes helper proc variant.

Reviewing the current status of updates

EXEC spMFClassTableStats 'MFCustomer' -- note the lastmodified and mflastmodified dates - it is the Max(date) on the table

make a change in M-Files to customer, then the following procedures allows you to specify the MFLastModified date as the filter for updating from MF to SQL

DECLARE @Update_IDOut INT,
        @ProcessBatch_ID INT,
        @LastModifiedDate DATETIME;

SELECT @LastModifiedDate = MAX(@LastModifiedDate) FROM dbo.MFCustomer AS mc

EXEC dbo.spMFUpdateTable @MFTableName = 'MFCustomer',    -- nvarchar(200)
                             @UpdateMethod = 1,   -- int
                             @MFModifiedDate = @LastModifiedDate, -- datetime
                             @Update_IDOut = @Update_IDOut OUTPUT,                    -- int
                             @ProcessBatch_ID = @ProcessBatch_ID OUTPUT,             -- int
                             @Debug = 0          -- smallint

SELECT * FROM dbo.MFProcessBatchDetail AS mpbd WHERE mpbd.ProcessBatch_ID = @ProcessBatch_ID

the following procedure has the mfUpdateTable with the last modified date switch built in. It will always reference to max(MFLastModified) date with no need to first define and set it

DECLARE @ReturnDate DATETIME, @ProcessBatch_ID INT, @Update_ID int
EXEC spMFUpdateTableWithLastModifiedDate @UpdateMethod = 1
                                          , @Return_LastModified = @ReturnDate OUTPUT
                                          , @TableName = 'MFCustomer'
                                          ,@Update_IDOut = @Update_ID output
                                          ,@ProcessBatch_ID = @ProcessBatch_ID output
SELECT @ReturnDate

SELECT * FROM dbo.MFUpdateHistory AS muh WHERE id = @Update_ID
SELECT * FROM dbo.MFProcessBatchDetail AS mpbd WHERE mpbd.ProcessBatch_ID = @ProcessBatch_ID