Comparing Two Tables that have Same Structures

There are two ways to compare data records in two tables.

1. Comparing two individual table objects;

2. Compare Data of two tables at Project Differences Dialog.

Comparing two Individual Tables

To comparing data records in two individual tables, you can choose two tables at database connection entering window. The names of two comparing tables can be same or different. If you want to compare data records in two tables that have different table name. You need to use this method to start data records comparing process.

Figure 1 Table Compare Option Dialog

Comparing two Tables at Project Difference Dialog

When you want to compare data records in two tables at Project Difference Dialog, you can highlight the tables you want to compare, then:

1. Select Action | Data Compare from the menu Project Differences Dialog.

2. Click the Data Compare button on the tool bar of the Project Differences Dialog.

3. Right click the highlight bar then select Data Compare from the context menu.

The SQLDBCompare program will compare the structures of two tables in the background. If the two tables has same structure detected (Same column layout, same column name and data type). A Select Columns to Compare Dialog appears (Figure 2). If the two comparing tables have different structures, a different dialog appears that allow you to select columns from two tables (See Comparing Two Tables that have different Structures).

Figure 2 Select Columns to Compare Dialog 1

Comparing Data records in two tables is a complex process. You might be interested in comparing a few columns in the two tables. And the data records in two tables can be sorted in a specified order.

By default all columns are selected and listed in the Select Columns to Compare Dialog. If you want to choose to compare data records other than the all columns, you can exclude columns by clearing the check box of each excluding column. If you want to list table columns in you specified order, you can highlight the column you want to move up or move down then click the up arrow or down arrow button next to the control. You can select the data records to be sorted by specified index or by user specified order. If the Use Index option is used, you can select the index that defined by the table listed in the Sort Order Combo Box. If you want to use the user specified order to sort the comparing records, you can select the Use Specified Order radio button. Then select the check box of each column name that you want to select in the Sort Order List. If you want to list data records at the order other than the listed order in the sort order list. You can highlight the column you want to move up or down then click the up or down arrow button next to the control to move the column to you desired position.

When comparing records in two tables, there are two ways to link the comparing records.

Link comparing records by all selected columns (the default option).
Link comparing records by selected columns.

When the first option is selected, the all selected columns values in one record in one table is compared in the another table. If the two records contain the same values, the two records are linked together in the Record Difference Dialog.

When Link comparing records by selected columns is selected, the selected columns values in one record in one table is compared the same columns values in another table. If the selected columns values are the same, the two records are linked together in the Record Difference Dialog even the unselected columns contains different values. By using this way, you will match up records by the selected columns.

To link comparing records by selected columns, you can need to select the radio button of Link comparing records by selected columns of Comparing Method. Then you can select sorting columns by selecting Use Index or User Specified Order option. If there is (are) column(s) in the Sort Order list, all the columns name in under the column “Link?” are marked with “=”. The column with “=” mean the column is selected link column. You can click on the “=” to select or deselect the link options.

Figure 3 Select Columns to Compare Dialog 2

After comparing columns and sorting order/Link columns are defined, you can start to compare the data records by click the Compare button. As you know, comparing data records is a very high resource usage process of your computer. If your comparing data tables contain a very large number of records, please make sure to limit them to a comparing reasonable numbers. The reasonable comparing number depends on the memory of you computer, number of comparing records and size of comparing record sizes.

Figure 4 Record Difference Dialog

After data record comparing process is finished, you will see Record Difference Dialog as shown in Figure 4. Total difference of the comparison is listed at the bottom of the screen. For records that detected different from the comparing opponents are listed in different back ground colors. The legend of the colors is listed at the status bar of the screen too.