Comparing Two Tables that have Different Structures

There are two ways to compare data records in two tables. 1. Comparing data records in two individual tables; 2. Comparing data records in two tables at Project Differences Dialog.

Comparing two Individual Tables

To comparing two individual tables, you can choose two options at database connection entering window. The names of two comparing tables can be the 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 get started.

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 table 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 have different structure detected (Different column layout, Different column name or Different data type). A Select Columns to Compare from Two Tables Dialog appears (Figure 2). If the two comparing tables have same structures, a different dialog appears that allow you to select columns from one table (See Comparing Two Tables that have Same Structures).

Figure Select Columns to Compare from Two Tables Dialog

Comparing data records in two tables is a complex process. You might be interested in comparing a few columns in the two tables instead of all columns. 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 from Two Tables Dialog. If you want to choose to compare data records other than the all columns, you can clear the check box of the columns that you want to exclude. 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 click the check box of each column name you want to include in the sorting column 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.

After comparing columns and sorting order are specified, 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 records 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 back ground color are listed at the bottom of the screen too.