Table Data Comparison Tool
Table Data Comparison Tool allows you to compare table data between tables in two different databases. The compared tables do not have to have the same name or same columns. Also, the comparison can be done between schemas on two different DBMS platforms such as Oracle and SQL Server. The tool has an intuitive interface for viewing mismatches and it can also produce a synchronization SQL script that will reconcile the differences.
For detailed information regarding this feature, see the User Documentation.
Currently database table data comparison is supported for Oracle, SQL Server, DB2, SQLite, HSQLDB, PostgreSQL, MySQL and Sybase.
The comparison can also be run on all supported platforms: Windows, Linux, MacOS X and Solaris.
The first screen of the Table Data Comparison Tool allows you to select the source and destination schemas or databases you want to compare.
Notice that the database comparison can be run against two schemas or databases on the same server.
If you only want to compare certain tables from the source schema/database, you can do that by unchecking 'Compare All Tables' check box and picking the individual tables for comparison. By default, all tables from the source and destination schema/database are compared.
Using the same mechanism, you also need to select the view, functions and stored procedures you want to compare, by default all objects will be compared. After selecting all the desired objects for the database comparison, click on 'Next' to proceed to the setting page.
The table selection screen allows you to pick the tables and columns you wish to compare. The screen also lets you set/override the primary key columns and enter a WHERE statement if you only want to compare a subset of the table's data.
The settings panel gives more control over the comparison process.
The settings panel is divided into three main categories; String, Numeric and Date/Time Types.
Each section lets you affect how the comparison is performed for that particular data type.
The results panel is divided into two parts, the upper section gives a summary of the compared tables whereas the bottom section shows details for the table that is selected in the upper list.
The result grid is colored so that pinpointing conflicting column values becomes trivial.
Generating SQL Data Synchronization Script
You can automatically generate a DML script that will synchronize the differences between the tables you compared. This script will contain the required SQL INSERT, UPDATE and DELETE statements to reconcile the differences based on the table data comparison results.
Saving and Loading Data Comparison Projects
The table data comparison tool lets you save all your comparison selections to a project file. The project files can then be checked into your source control system and shared between different entities in your organization. This facilitates running comparisons on a regular basis to minimize costly mistakes.
To save your comparison settings, click on the 'Save Project' button on the last screen of the data compare wizard. To open a saved project file, click on the 'Open' button on the first screen.