Business Central Delta Migration with DataMigrate Pro

Records that have been transferred successfully do not have to be transferred to Business Central again – unless they have been changed in the source database in the meantime. Navision has an additional column called timestamp in every table by default. In normal operation it is used to detect changes to records and to display the familiar message “… Another user has modified the record …”.

DataMigrate Pro makes use of this mechanism and identifies, based on the timestamp, which records have changed since the last transfer.

A log table automatically stores all records, together with their timestamp, for which Business Central has confirmed a successful transfer.

An intelligent, dynamic query ensures that in the operating mode --changesonly 1 only those records are transferred again whose current timestamp has not yet been recorded in the log table. In addition, the system also detects records that have already been logged but have since been deleted in the source system – for example, when entries were removed after the migration started.

The following command therefore transfers only new or changed master data and setup data to Business Central:

DataMigratePro -t putalldata --tablegroup "1,2" --changesonly 1 --automapping

Here is an example of the query based on the customer table, which passes the changes and the records to be deleted to DataMigrate Pro.

;WITH T AS (SELECT CAST(T.[timestamp] AS BIGINT) AS timestamp, ROW_NUMBER() OVER(ORDER BY T.[No_]) [RowNumber], T.[No_],T.[Name],T.[Search Name],T.[Name 2],T.[Address],T.[Address 2],T.[City],T.[Contact],T.[Phone No_],T.[Telex No_],T.[Our Account No_],T.[Territory Code],T.[Global Dimension 1 Code],T.[Global Dimension 2 Code],T.[Chain Name],T.[Budgeted Amount],T.[Credit Limit (LCY)],T.[Customer Posting Group],T.[Currency Code],T.[Customer Price Group],T.[Language Code],T.[Statistics Group],T.[Payment Terms Code],T.[Fin_ Charge Terms Code],T.[Salesperson Code],T.[Shipment Method Code],T.[Shipping Agent Code],T.[Place of Export],T.[Invoice Disc_ Code],T.[Customer Disc_ Group],T.[Country_Region Code],T.[Collection Method],T.[Amount],T.[Blocked],T.[Invoice Copies],T.[Last Statement No_],T.[Print Statements],T.[Bill-to Customer No_],T.[Priority],T.[Payment Method Code],COALESCE(NULLIF(NULLIF(T.[Last Date Modified],'1753-01-01'),'1900-01-01'), '') [Last Date Modified],T.[Application Method],T.[Prices Including VAT],T.[Location Code],T.[Fax No_],T.[Telex Answer Back],T.[VAT Registration No_],T.[Combine Shipments],T.[Gen_ Bus_ Posting Group],T.[Post Code],T.[County],T.[E-Mail],T.[Home Page],T.[Reminder Terms Code],T.[No_ Series],T.[Tax Area Code],T.[Tax Liable],T.[VAT Bus_ Posting Group],T.[Reserve],T.[Block Payment Tolerance],T.[IC Partner Code],T.[Prepayment %],T.[Primary Contact No_],T.[Responsibility Center],T.[Shipping Advice],dbo.DecodeDateFormula(CAST(T.[Shipping Time] AS VARBINARY(32))) [Shipping Time],T.[Shipping Agent Service Code],T.[Service Zone Code],T.[Allow Line Disc_],T.[Base Calendar Code],T.[Copy Sell-to Addr_ to Qte From] FROM [Demo Database NAV (6-0)].dbo.[CRONUS AG$Customer] T WITH (NOLOCK))
   SELECT T.*, 1 [Type_of_Change], 'Field1=0('+T.[No_]+')'[EncodedPrimaryKey]
     FROM T
    WHERE NOT EXISTS 
	      ( 
	      SELECT 1
            FROM [Migration].dbo.[Transfer] TL WITH (NOLOCK)
	       WHERE TL.[TableNo]=18
	         AND TL.[EncodedPrimaryKey]='Field1=0('+T.[No_]+')'
	         AND TL.[TimestampValue]=CAST(T.[timestamp] AS BIGINT)
          )
UNION
   SELECT T.*, 2 [Type_of_Change], TL.[EncodedPrimaryKey]
     FROM [Migration].dbo.[Transfer] TL WITH (NOLOCK)
LEFT JOIN T ON TL.[EncodedPrimaryKey]='Field1=0('+T.[No_]+')' AND TL.[TableNo]=18 
    WHERE T.timestamp IS NULL AND TL.[TableNo]=18