Compare data of two tables

How to compare data of two tables in the same database

When building, testing, or maintaining a database, you may need to compare data in two similar tables of the same database.

Note

  • If columns of the tables have different names, you may need to map the individual columns together as well.
  • Make sure the data of the columns you map together is of a similar type.

To map data of two related tables within the same database, follow these steps

1. Click New Data Comparison.

New Data Comparison

2. Specify target and source connection names and databases you want to compare. And then click Next.

Target and source connection

3. On the Options page, adjust the settings to your needs and click Next.

Adjust options

4. On the Mapping page, map together the tables you want to compare. To learn how to map tables, see Select tables and views.

Pay attention, that when you compare, the tool automatically maps tables and columns that have identical names in a source and a target database.

To map two different tables in the same database, proceed with the following steps

1. Click New Data Comparison.

New Data Comparison

2. Specify target and source connection names and databases you want to compare. And then click Next.

Target and source connection

3. On the Options page, adjust the settings to your needs and click Next.

Adjust options

4. On the Mapping page, select the table you want to map and click Unmap. If the columns of the tables have different names, you may need to map the individual columns together as well. Ensure that the data types of the columns you map together are compatible.

Unmap the table

dbForge Data Compare automatically maps tables with the same name and schema (owner). To be able to map, for example, two tables, you first need to unmap all the tables. For this, click Exclude/include all objects.

Exclude objects

5. Click Map Objects.

Map objects

6. Under Target objects, clear Hide mapped objects.

Hide mapped objects

7. Select the target table you want to map with the source one and click Map.

Map the table

8. Click Close. You’ll see that the source table is mapped with the desired target one.

To match rows in the two data sources, Data Compare for SQL Server requires a comparison key for each table.

Map tables

For this, click the drop-down menu and select Custom.

Select Custom

In the Column Mapping window, you can set the key and verify which columns have been mapped. Note that the columns with the same names are mapped automatically. As you can see the column BusinessEntityID in the source table does not have a similar column in the target database. It means that you need to map the columns manually. Just click the drop-down menu under the target table and select the desired column.

Select the desired column

Finally, click OK.

Also, dbForge Schema Compare maps automatically objects with the same schemas. To check that the schemas have been mapped, click Map Schemas.

Map schemas

To learn more on how to map schemas, refer to Map schemas.

10. To initiate the comparison process, click Compare.

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Data Compare for SQL Server.
All Features

All features

Get acquainted with the rich features and capabilities of the Data Compare in less than 5 minutes.
Request a demo

Request a demo

If you consider employing the Data Compare for your business, request a demo to see it in action.
Ready to start using dbForge Data Compare for SQL Server?