Table Data Comparison Tool allows you to compare data in one or more pairs of tables. The tables can be in the same or different schemas/databases and they don't need to have the same name or columns. Comparison can be done between tables in different DBMS platforms such as Oracle and SQL Server.
You can invoke the Table Data Comparison Tool from the 'Tools' menu in the main toolbar of DB Solo.
The first screen of the comparison tool allows you to select the schemas/databases that contain the tables you wish to compare. The tree control on the left hand side allows you to select 'source' of the comparison and the right side lets you pick the 'destination' schema.
Figure - Select Schema/Database Screen
After selecting the schemas/databases click on 'Next' to move to table selection panel.
The table selection screen lets you select the tables you want to include in the comparison. The tree control on the left hand side lets you select the tables and their columns to be included in the comparison. Notice that you always have to select the primary key columns of a table if you wish to include the table in the data comparison. The right-hand side lets you select what the primary key columns of the table are. By default the actual primary key constraints' columns are selected, if one exists. If the table does not have a primary key constraint, you must select a set of columns that uniquely identify rows in the table, i.e. no two rows can have the same values in the primary key columns. If the selected columns do not uniquely identify rows in the table, the comparison results are not accurate.
The right-hand side also lets you pick the table that the selected table will be compared against. By default a table with the same name will be selected from the destination schema/database, if one exists. You can also select and/or override the primary key columns for the mapped (destination side) table.
The second tab of the panel allows you to enter a where statement for both the source and the destination side. This is useful if you don't wish to compare all rows in the tables. For example, lets say your table contains sales data and you only wanted to compare the last day. In this case you would enter a where statement that would only return sales data for the day you want to include.
The various options in the settings screen allow you to fine-tune the comparison process. All settings will get persisted when you close DB Solo, so you don't have to re-enter the same settings next time you want to run the comparison. You can configure the following settings:
Strings
Numeric
Date/Time
Figure - Comparison Settings
After applying all the settings you wish to change, click on 'Next' to start the comparison. Notice that if the number of rows to compare is large , the comparison may potentially take several minutes to complete.
After DB Solo has completed the comparison process, you will see the results screen that is divided into two sections. The upper part lists all the compared tables and gives a summary of the comparison process. When you select a table in the upper section, the comparison result details will be shown in the bottom section for that table. The bottom screen has the following four tabs:
Figure - Results Panel
The table data comparison tool has a Synchronization Script creator which allows you to create a DML script that contains all required SQL DELETE/INSERT/UPDATE statements to synchronize the selected source tables with their counterparts on the destination side. First you need to select the tables and individual rows to be included in the synchronization script by checking the appropriate checkboxes in the 'Sync' columns. When you check the Sync checkbox on the table list, all rows that are different will automatically be selected for that table.
The first screen of the synchronization script creator dialog allows you to change the script generation settings. The settings screen will let you change the keyword case of the SQL statements, the statement separator type and whether identifiers are shown in brackets, quotes or neither. You can optionally also include the tables' schemas/owners in the generated script.
The last page of the Synchronization Script Wizard will show the DML script based on the data comparison results and your settings. The buttons on this screen let you save the script to a file or copy it to the system clipboard.
The 'Load Project' button on the first page of the comparison tool allows you to open projects you have saved earlier. The project files contain information about the source / destination databases you have selected, tables/columns you selected for comparison as well as all the comparison settings from the comparison options page of the tool.
After completing a table data comparison, you can save your settings on the last page of the comparison tool by clicking on the 'Save Project' button.
Data comparison can be run from a command shell using settings from a saved project file. This allows you to run data comparisons regularly using cron or a similar mechanism. The following shell command will run a data comparison from settings in 'myproject.xml':
commandLine -dataCompare myproject.xml
where 'myproject.xml' is a project file. You can use project files on different machines as long as the configured server connection names match. The project files are xml text files that can be edited manually outside DB Solo.
The command line tool will output errors and other messages in dataCompare.log file.
| Back to Index | DB Solo www.dbsolo.com support@dbsolo.com |