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:
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.
|