KS DB Merge Tools logo KS DB Merge Tools
Documentation
MssqlMerge logo for SQL Server
aka MssqlMerge
KS DB Merge Tools for Oracle logo
KS DB Merge Tools for MySQL logo
KS DB Merge Tools for PostgreSQL logo
KS DB Merge Tools for SQLite logo
AccdbMerge logo
KS DB Merge Tools for Cross-DBMS logo

Batch Data Diff Tab

  • Opened from: Batch data diff toolbar action (Ctrl+Shift+B keyboard shortcut) and from Home tab. In the 'Data slice' mode this tab is opened from the Diff profile editor tab
  • Applicable tab-specific toolbar actions:
    • Export to Xlsx or Json
    • Generate automation script stub for data changes summary (with LogTo(), DbOpen(), BatchDataDiff() and commented ForEach and DataMerge() statements relevant for the current databases)
    • Show all, new and changed, new, changed
    • Jump to the last, next, previous, first change
    • Select all, none, invert selection on the left, all, none, invert selection on the right: selection can be changed using any of left or right selection buttons
    • Merge data from selected tables to the right-side database, to the left-side database
    • Delete data from selected tables on the left-side database, on the right-side database
  • Applicable object types: Tables and Views

This tab allows us to compare data for multiple tables, views, custom mappings and queries, providing a summary of data changes for the whole database:

for SQL Server, batch data diff tab

This tab is also used to compare data for the Data slice (aka 'Data slice' mode). In the 'Data slice' mode tab title contains a specific tab icon and the name of the data slice.

Top panel contains:

  • Run action, which is also available by F5 keyboard shortcut. Once started, it is changed to the Cancel button to stop execution (may take some delay).
  • a number of self-descriptive Calculate and Objects execution options which are available with Alt and action key from keyboard. Action key is underlined when you hit the Alt key. So, for example Alt+V shortcut switches 'Show views' option
  • option Only total retrieves totals using COUNT_BIG(*) query, option use sys.dm_db_partition_stats uses system dynamic management view, which usually provides much faster result but may require specific permissions
  • quick filter button that allows to filter object list by name. This can be very convenient for large object lists. This action also available with Alt+F keyboard shortcut
  • Create data slice command to create a new data slice containing only selected objects. New diff profile becomes available in the Data slices section of the Diff profiles tab. This command is disabled if the application has no active diff profile loaded. And this command is not available at all if the tab is opened in the 'Data slice' mode.

Result columns:

  • Table name - name of the table or view, title of custom mapping or custom query
  • Type - table, view, custom mapping or custom query
  • Changed columns - this table in left and right projects has different columns. Why this important - even if it shows 0 changes, this result is based only on common columns. Columns which exist in only one side - they still may have some data missing in the other side and this is not counted as change
  • Common key - table has compatible primary key on both sides, which consists of the same columns with the same data type. Common primary key is required to compare data, without it only total record count can be calculated. Key also can be defined in the diff profile, not only for tables but also for views
  • DL (left) and DL (right) - table dependency level calculated using information about foreign keys. If the table has no foreign keys - its DL is 0. Tables with DL 1 have foreign keys referenced only to tables with DL 0; tables with DL 2 depend on tables with DL 0 and 1, DL 3 depends on DLs 0, 1 and 2 and so on. This information can be used if you want to merge all tables one by one. If you import data - DL 0 must be processed first, then DL 1 and so on, otherwise you can get a foreign key violation error. If you delete data - this is an opposite case: you should process max DL first, for the same reason of possible constraint violation error. Note that DL not always can be calculated - tables may have circular references. See Table DL for more information.
  • Total (left) and Total (right) - total record count
  • New (left) and New (right) - new record count
  • Changed - changed record count, calculated only for common table columns (which exists in both sides)
  • Error - result calculation error, if any, may help to understand what was wrong (for example - timeout or missing permissions) and how this can be fixed

Tables without common key are compared just by row number.

Totals values are highlighted in red if they are different (so, changed records should not produce Total highlight). Changed records are also highlighted as red if Changed > 0. Unlike Total and New there is no Changed left and right, it is just a single value because if some record is changed then this change should be counted as +1 for both sides. New value is highlighted in green if it is > 0.

Free version limitations:

  • The whole tab is not available

Last updated: 2024-10-21