Contents 

AdeptSQL Diff Online Help

Comparing table data in AdeptSQL Diff

Introduction

Although AdeptSQL Diff is primarily a schema comparison tool, it also includes a subsystem called DataDiff which allows to compare and synchronize data between any selected pair of matched tables. The DataDiff functionality is considered optional: it is available to all users while the program runs in trial mode, but after that requires a special "DataDiff-enabled" license.

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

(Now let's say it again, because some people seem to find this confusing:  the DataDiff command is normally disabled. It gets 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):

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 (synchro) 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 must 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.