( Pro Edition )
The Data Compare is a tool used to compare data from one database or table to another and can be reached via Tools | Data Compare.
In this topic
User Interface Overview
Below is a screenshot and explanation of different on-screen items:
Data Compare window
In the above example, a compare project "test.cmp" has been loaded. It connected to both a source (1) and destination (2) database. It lists mappings (3), with a key column either defined by the primary key of the table (table ADJSTMNT) or a hand-picked compare key (table CASETEST2).
Click the green arrowed Start Compare Process button to start comparing the data.
Loading & Saving a "compare project"
You can load and save your mappings and connection information to a "compare project"-file (extension ".cmp").
When you have saved a project, you can re-load it and the Data Compare will connect to both the source and destination database and displaying the table and column mappings. Any mappings before loading the file will be removed.
If you want to use a particular set of mappings for a different set of databases, you can connect to the source and/or destination databases first and then load the file, the Data Compare will then ask you if you want to keep the current (source or destination) connection.
Keep Current Connection confirmation dialog
Such a mapping is easily created by dragging the source table to the destination table and dropping it there. After such a drop, an attempt will be made to match column name - this matching process can be adjusted on the Options tab - it does a case insensitive match and filters any non-standard characters such as ' # $ _ etc...
Any columns that don't get matched will stay empty until you drag 'n drop a source column to the destination column.
The compare order and order of updating data can be controlled via the UP/DOWN arrow buttons on the Data Compare toolbar. This can be very useful if your target database has foreign key constraint defined, additionally, you can let Database Workbench determine the order automatically by examining foreign key dependencies, use the Determine Table Order button for that.
Determine Table Order button
You can also use the Auto-map button to automatically match table & column names for all available tables.
By default, the Data Compare will open an additional connection to your databases and optimize the running process for concurrency - that is, you will be able to continue working with Database Workbench.
During the data compare process, tab page Process & Results is activated.
Process & Results during and after the data compare process
The top part of the window holds the list of compare mappings, the current status under Action and the results.
The bottom part of the window is the per compare mapping result list. You can view which records are available in the source only, destination only or which records are different, to the actual value of each field in the table.
You can choose to update the target database directly, or collect a script, check and run manually. Items included should be checked, both on the compare level and individual rows. Select All and Deselect All buttons will help you.