Comparing table data in AdeptSQL Diff

Introduction

Starting from AdeptSQL Diff ver. 1.50, the program can compare and synchronize not only database schema, but also data between any selected pair of matched tables. This functionality, referred to as DataDiff, is optional: it is available to all users while the program runs in trial mode, but have to be activated (purchased) separately when the trial expires or if you have an older license. You can find more information about the upgrade issues on our site.

Besides the general description below, you can find additional information about DataDiff here:

Table-level DataDiff options
Column-level DataDiff options
Special situations you may encounter using DataDiff
DataDiff configuration options

Note that the integrated DataDiff can not be used to compare data between two tables in the same database or between arbitrary tables in two databases. It only compares tables associated with each other during the schema comparison.  A stand-alone DataDiff tool that can compare multiple arbitrary tables, views or queries from one or several databases is currently under development at Adept SQL Tools.

Getting started

To compare data, start comparing your two databases as usually, then select an item in the schema tree representing a pair of matched tables. If the tables contain data, you will see that "Compare data" command in the context menu and the equivalent button on the toolbar are enabled. You can also run DataDiff for a single (non-matched) table, in which case it works as a simple data explorer and scripting tool.

(Now let's say it again, because some people seem to find this confusing:  the DataDiff command becomes enabled only when you select a single item in the schema view, representing the pair of tables to be compared. And at least one of the tables must contain data. At any other time, both the menu item and the toolbar button are grayed-out and not clickable.  This is simply because there is no data to compare and has nothing to do with your copy of Diff running in trial mode or having a wrong (not DataDiff-enabled) license!)

The first thing you see after clicking the "Compare Data" command is the DataDiff comparison configuration dialog.  It consists of two pages, arranged wizard-style. First, you can configure table-level parameters (like the row filters for one or both tables) and then you can change comparison settings for each specific column.  The Diff usually provides some reasonable default settings for the comparison, so you don't have to change anything unless you know what you are doing.  The only exception is when your tables don't have any primary key, in which case you must specify at least one key column.

Click on the images below to read more about the DataDiff configuration dialog:

Table-level DataDiff options

Column-level DataDiff options

The DataDiff window

Once the tables are loaded and compared, the data is displayed in the DataDiff window:

As you can see, all controls in the DataDiff window look and act very similar to those in the schema tree panel and the highlighting of changes is exactly the same.

[Diff 1.90 or later]: DataDiff now has the notion of "minor changes". Those are differences in data values which are detected by the comparison algorithm, but are considered "minor" and therefore don't require synchronization. These are currently lowercase/uppercase differences for column configured as case-insensitive and changes in numeric columns below the specified tolerance value.  Columns with minor changes are displayed one under another just as the "normal" changed columns, but don't have the blue/red color markup.

The buttons on the toolbar are (in left-to-right order):

• Refresh: compare these tables again, starting with the column selection dialog.
• Filter pull-down menu: Allows to hide all unchanged rows, leaving in the grid only the new or changed ones.
• First/Prior/Next difference: brings into view and selects the first/prior/next modified row, scrolling data in the grid if necessary
• Synch left/Synch right: produces a data synchronization script for either of the tables. The script is produced for each of the selected rows. If no rows are selected, it is produced for the whole table. The resulting script opens in the Code Viewer window, ready for execution.

All relevant commands are also available through the right-click context menu. The status bar at the bottom displays the numbers of identical, different and one-table-only records.

Scripting

Once the tables are compared and the data is displayed in the grid, you can produce SQL scripts from the context menu, by clicking toolbar buttons, by using hotkeys Ctrl-Alt-LEFT / Ctrl-Alt-RIGHT or by drag & drop. Please read a more detailed explanation of the Drag & Drop functionality in AdeptSQL Diff (the CREATE, DROP and ALTER commands mentioned there would become INSERT, DELETE and UPDATE).

DataDiff will script one or more selected rows or, if no rows are selected, it will script the entire table. When two tables are compared, DataDiff can generate up to 6 different SQL scripts: INSERT, DELETE and update scripts, each for either the "left-hand" or the "right-hand" table. Commands for certain kinds of scripting will be disabled if they don't make sense for the currently selected rows. E.g. if you select an identical row, you wouldn't have the command to make update scripts for it, if it's a "right-only" row, you won't have commands to script from the left-hand table, etc. When DataDiff is started with just one table, it can produce either INSERT or DELETE script for this table.

When you ask DataDiff to produce an update script, it will contain a mix of INSERT, DELETE and UPDATE statements, depending on whether records in the target table should be added, removed or modified to match the source one. Some additional statements, like "SET IDENTITY_INSERT" or transaction statements may be included in the script whenever appropriate.

Sometimes the DataDiff has more than one way to script a specific column. For example, BLOB columns can (and should) be scripted  such that the data is transferred directly between the two databases, rather then included into the script as literals.  Or you might need to specify literal values to use in INSERTS when the "source" column is missing.  All such adjustments can be made in the column-level DataDiff configuration dialog before the comparison begins.

As always, the produced scripts appear in the SQL Code Viewer, where you can edit and/or execute them. It is recommended that you always preview the generated script before sending it to the database. Make use of the transaction control feature of the SQL viewer to protect your database from any possible data loss.