Successfully transferred data records do not need to be transferred to Business Central again – unless they have been changed in the source database in the meantime. Fortunately, Navision has an additional column called timestamp in every table by default. This is used in normal operation to recognize changes to data records and to display the familiar message “… Another user has changed the data record …”.
DataMigrate Pro makes use of this mechanism and recognizes which data records have been changed since the last transfer on the basis of timestamp.
All data records, including the associated timestamp, for which Business Central has confirmed a successful transfer are automatically saved in a log table.
An intelligent, dynamic query ensures that only those data records whose current timestamp has not yet been recorded in the log table are retransmitted in the --changesonly 1 operating mode. In addition, the system also recognizes data records that have already been logged but have since been deleted in the source system – for example, if entries were removed after migration began.
The following command therefore only transfers new or changed master data and setup data to Business Central:
DataMigratePro -t putalldata --tablegroup "1,2" --changesonly 1 --automappingHere is an example of the query using the customer table, which transfers the changes and the data 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