Mapping tables and columns
Published 04 June 2013
When you have selected your data sources, SQL Compare automatically maps tables and columns with the same name in the source and target data sources.
However, SQL Compare also allows you to map together tables and columns with different names. This can be useful to prevent data loss when deploying tables or columns that have been renamed.
For example, if you map the table TableA in the source to the same table that has been renamed as TableB in the target, SQL Compare will compare the table as an object that exists in both data sources. When you deploy the table, the name change will be scripted using the sp_rename system stored procedure; the table is not dropped and re-created.
To compare tables and columns that are not automatically mapped, click the Table mapping tab of the Project Configuration dialog box:
The upper pane displays tables that are fully Mapped or have Partial mapping. The lower pane displays Unmapped tables.
To set the column mappings for a table, click the Status link for the object you want to re-map.
To map tables:
- On the Table mapping tab, select an Unmapped table that you want to map from the source database.
- Select the Unmapped table that you want to map from the target database.
SQL Compare moves the tables to the upper pane. Map.
To unmap tables:
- On the Table mapping tab, select a Mapped table, or a Partial mapping.
- Click Unmap.
SQL Compare moves the tables to the lower pane.
If you want to compare columns in a table and the column names are different, you can map the columns as required.
SQL Compare will try to automatically map columns with compatible data types and similar names. When the comparison is complete a warning will be displayed in the comparison results if automatic mapping has taken place. This automatic mapping of similar columns can be disabled by deselecting the Auto-map similar columns option on the project options tab.
To map columns:
- On the Table mapping tab, click the Status box of a Partial mapping.
A dialog box is displayed. For example:
- Select an Unmapped column that you want to map from the source database.
- Select an Unmapped column that you want to map from the target database.
SQL Compare moves the columns to the upper pane of the dialog box. Map.
- Click Close.
To unmap columns:
- On the Table mapping tab, click the Status box of a Mapped table or a Partial mapping.
A dialog box is displayed for you to specify the column mappings, as shown above.
- Select a Mapped column.
SQL Compare moves the columns to the lower pane of the dialog box. Unmap.
- Click Close.