Datatific Review Learning Center

How To Compare Two Tables

Whether you are comparing two tables from the same database, different databases or even different database types (Access, Excel, Oracle, SQL Server), Datatific Review can do it. Even if you have dissimilar data like columns with different names and/or different data types, Datatific Review can compare it.  You can also concatenate or parse data if necessary to make comparing data easier.

The Compare feature provides a stepped processed for comparing two tables with features and options to make the task easy. The basic steps for comparing two tables are:

  1. Specify the first table to compare
  2. Specify the second table to compare
  3. Map the columns in each table to compare
  4. Specify the unique keys in the tables
  5. Compare the tables
  6. View and analyze the results
  7. Save the compare settings
Buy now
Try for free
More Info
Data Navigator Tutorial
Buy now
Try for free
More Info
Demo (14 min.)

How-To Details

To compare two tables, open Datatific Review, select the Compare tab and follow the steps below.

Specify the First Table

Select Data Source 1 in the Compare setup. Select the data source type, enter the data source settings and then click the Connect button. The screenshot below shows the settings for an Access database.

Access Data Source

After connecting, use the Data Source 1 left sidebar to expand the Table or View folder and select the table or view to compare.

Select Data Source 1 Table

Filter the data as desired using the data tree navigator and/or the table column filtering.

Specify the Second Table

Select Data Source 2 in the Compare setup. Select the data source type, enter the data source settings and then click the Connect button. The screenshot below shows the settings for a SQL Server database.

SQL Server Data Source

After connecting, use the Data Source 2 left sidebar to expand the Table or View folder and select the table or view to compare.

Select Data Source 2 Table

Filter the data as desired using the data tree navigator and/or the table column filtering.

Map the Columns to Compare

Select Map Data in the Compare setup. If both tables have the same column names, the easiest way to map the columns is to select the Match All command on the Compare ribbon. If the tables have different columns names, then it is generally best to select the All From 2 command to automatically add all Data Source 2 columns and then manually map any unmapped Data Source 1 columns.

Match All

Use automation and manual mapping to map the columns to compare. Delete or exclude the columns you don't want to compare.

Tip: Use the Expressions feature in Datatific Review to concatenate or parse data if necessary to normalize column data with the other table.

Map Columns

Specify the Unique Keys

In Map Data, specify the columns that form unique keys - or values - for the rows. This is how new rows, deleted rows and changed rows are determined. The keys can be one or more columns. Specify the keys by clicking on the Map Setting value. This toggles between Compare, Key and Exclude.

Map Keys

Compare the Tables

Select Compare Data in the Compare setup. Before comparing, select from one of the various options available. Note that the default compare direction is from Data Source 1 to Data Source 2 which means Data Source 1 is considered "before" and Data Source 2 is considered "after". You can change the direction by clicking on the Compare Direction button.

When ready, click the Compare button.

Compare Data

View and Analyze Compare Results

After the compare is complete, the compare results are displayed with a graphical summary.

Compare Summay

You can also view the summary in tabular form.

Compare Summay

Use the View Results navigator to select an item to view in more detail.

Compare Summay

For example, click on All Compare Data to view all compare results. Note the results are color coded for easy viewing (you can change the colors in Options).

Click to see larger image

Unequal results are displayed by stacking the two rows on top of each other. Changed values are color coded for easy viewing.

Unequal Resutls

Save Compare Settings

You can save the compare settings for reuse again later. Note this only saves the settings (i.e., the data source connection information, compare map and compare options) - not the data.  This makes it even easier to run the compare again.

Note: Data filters are not saved. If you filtered the data before comparing you will have to filter it again the next time.

To save the compare settings click the Save Compare button on the ribbon. If the compare has not been saved before, you will be prompted for a filename and location.

Save Compare

To save a previously saved compare to a different file, select File > Save > Save As from the ribbon. You will be prompted for a filename and location.

Compare Save As

To open a previously saved compare, select File > Open > Open Compare from the ribbon.  You will be prompted to specify the filename and location.

Open Compare

Refer to the Datatific Review Users Guide for more details on how to compare data.


Back to Learning Center Home