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.
2. Specify target and source connection names and databases you want to compare. And then click Next.
3. On the Options page, adjust the settings to your needs and click Next.
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.
2. Specify target and source connection names and databases you want to compare. And then click Next.
3. On the Options page, adjust the settings to your needs and click Next.
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.
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.
5. Click Map Objects.
6. Under Target objects, clear Hide mapped objects.
7. Select the target table you want to map with the source one and click Map.
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.
For this, click the drop-down menu and 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.
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.
To learn more on how to map schemas, refer to Map schemas.
10. To initiate the comparison process, click Compare.