How to compare SQL Server database tables with different names

When comparing live database or database backups using ApexSQL Data Diff, tables with the same name are compared automatically. But what happens with tables and columns with different names when compare SQL tables for differences?

By default, they are excluded from the comparison process and need to be mapped manually using the Object mapping feature. This feature also allows changing tables paired by default, i.e. unmapping them and creating customized comparison pairs. This can be helpful in scenarios where the same tables are differently named in the development and the production database, and data needs to be pushed from the development database to the production one.

To access the Object mapping feature, click Show Advanced tabs in the project dialog and navigate to the Object mapping tab. This tab lists the objects in both the source and the destination data sources that are automatically paired in the Mapping grid.

Object mapping - Map

The lists below the grid contain the objects which haven’t been paired automatically in both the source and destination data sources respectively. Pairing these objects is easy; simply select an object from the source data source and the corresponding one from the destination data source and click the Map button. This will automatically pair the objects and map the corresponding columns.

This process is reversible: any of the mapped tables can be unmapped selecting an object pair and clicking the Unmap button. Using this method, the complete mapping process can be customized.

Object mapping - Unmap

Can column mapping in certain tables also be customized?

Yes, and column mapping feature can be used for the columns in both automatically and manually mapped tables. To access this feature, click on the Mapped column’s cell of the object pair whose column mapping you need to change. The actual column mapping process is similar to the one used when mapping objects.

This is particularly useful in situations where two tables share the same name, but their column names are different; in that case each column pair needs to be mapped manually in order for the data stored within them to be included when compare SQL tables for differences

April 2, 2015