When comparing data sources using ApexSQL Data Diff, tables with the same name are mapped automatically. But, what happens with tables and columns with different names when SQL tables are compared to check for data 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 users to change 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.
In order to compare SQL tables and columns with different names, follow these steps:
- Run ApexSQL Data Diff
- Under the Data sources tab of the New project window, set desired data sources:
-
To access the Object mapping feature, expand the Advanced options in the New project project window 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 Mapped grid.
-
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.
-
If column names are different as well, the 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 needs to be changed. The actual column mapping process is similar to the one used when mapping objects:
Quick tip:
The Column mapping feature 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 SQL tables are compared to check for data differences
-
Once all objects are mapped, click the Compare button from the bottom-right corner and once the comparison process is done, the custom mapped objects will be shown in the Compared tab of the Results grid:
Now, the data differences between custom mapped objects can be reviewed and exported to one of reports.
April 2, 2015
