Contents

Selecting Tables and Views

After you’ve created a project and you selected your data sources, you can specify tables, views, and columns to be compared. Please, note, that Data Compare for SQL Server automatically maps tables and views with the same name and schema (owner).

But keep in mind, that if there is any difference between the data sources, for example, if two views have different names, they may not be mapped automatically.

You can map such objects manually using the Mapping tab of the New Data Comparison wizard.

The Mapping tab allows you to:

• Select the comparison key for each table or view
• Select the columns to be compared
• Map the objects
• Unmap the objects
• Map schemas
• Return mapping options to defaults
• Design a custom query
• Configure filter options

Selecting the comparison key for each table or view

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

The tool automatically selects a comparison key if:

• tables contain a matching primary key, unique index, or unique constraint
• views contain a matching unique clustered index

To learn more, see What’s a comparison key?

If Data Compare for SQL Server is unable to identify a suitable comparison key for a table or view, a corresponding warning will be displayed.

To set the comparison key for an object, click an arrowed button in the corresponding row of the Comparison Key column.

Select < Custom… > and the Column Mapping window opens where you can select the columns that will comprise the key.

Note:

• You can not use as a comparison key those columns whose data type is image, ntext, nvarchar(max), sql_variant, text, varbinary(max), varchar(max), or xml.
• Data Compare doesn’t allow specifying a comparison key if a backup is used as a data source.

For more information about comparison keys, please refer to the topic: What’s a comparison key?

Selecting the columns to be compared

On the Mapping tab of the Data Compare tool, SQL Data Compare the number of columns that will be compared for each table or view is displayed.

Data Compare allows customizing the comparison to consider only specific columns.

To select the columns to be compared, double-click the table or click … at the end of a row. A dialog box will be displayed with checkboxes to include or exclude the columns.

Note:

• You can not map the columns having different names.
• You can not map the columns having different data types.
• You can not exclude the columns that are used for the comparison key.

Mapping objects

By default, when you select that databases to be compared, configure comparison options and move to the Mapping tab of the New Data Comparison wizard, Data Compare for SQL Server automatically maps all the objects having the same names and data types in the selected databases.

To map objects that stay unmapped, on the Mapping tab, click the Map Objects button.

In the Objects Mapping window that opens, you can select a table or view you want to map.

Having selected the required objects, click the Map button. The selected objects will appear in a list of mapped objects on the Mapping tab.

Note:

If tables or views that you are mapping contain columns with incompatible data types, Data Compare for SQL Server cannot compare those columns and they will be excluded from the comparison. After the objects have been mapped for comparison and synchronization, you may encounter mapping warnings, displayed as Caution icons. We recommend study warnings thoroughly before moving on.

Unmapping objects

You can unmap objects in several ways:

• Click the Unmap button in the upper menu bar. In this case, all the mapped objects will be unmapped.
• Select a required object from a list and click the Unmap button in the upper menu bar. In this case, only selected objects will be unmapped.
• Select a required object from a list, right-click it, and then select the Unmap option from the menu that appears.

Note:

Other options available in the menu that appears after right-clicking the object from a list:

• Exclude all but this
• Unmap all but this
• Unmap all invalid.

Mapping schemas

When you start a new data comparison project and you select your data sources, Data Compare for SQL Server automatically maps objects with the same schema.

However, the tool allows comparing objects having the same name, but belonging to different schemas. It can be useful if schemas were renamed and you want to synchronize data between a database with new schema names and a database with old schema names.

To achieve that, you need to map the schemas as required.

To map schemas:

1. Switch to the Mapping tab of the New Data Comparison wizard.

2. Click the Map Schemas button on the toolbar.

3. In the Map Schemas dialog window that will appear, map the source schema with the target schema as required. To do this, click the drop-down arrow button in the corresponding Target column cell and select the required schema from the drop-down list.

4. After you’ve mapped all schemas you need, click OK. If you want to restore original mapping, click the Reset button.

Note:

Data Compare for SQL Server supports only one-to-one schema mapping. If you need to synchronize data between one source schema and several target schemas or vice versa, you need to perform several comparison and synchronization operations.

Returning mapping options to defaults

Data Compare for SQL Server allows you to quickly and easily return mapping options to defaults.

To do this, click the Reset button in the toolbar. The default mappings will be restored.

Designing a custom query

You can use the Custom Query option to compare custom results received after query execution instead of comparing whole tables or views.

To do this: 1. Click the Custom Query button on the Mapping tab of the wizard.

2. In the Custom Queries Mapping dialog window that opens, select the Query or Table or View radio buttons both for the Source and the Target.

3.

• If you select the Table or View option you will be able to map tables or views from the list.
• If you select the Query option for the Source, the Target or both, write query name in the field near Name (if the name assigned by default is not appropriate).

4. Type the needed query in the SQL field.

5. Click Validate to validate your queries, if necessary.

6. Click Map.

7. Click OK to close the dialog window.

Tip:

• Click the Cancel button to close the dialog window and discard mappings.

• Click the Reset button to restore the default mapping of objects.

Configuring filter options

By default, Data Compare for SQL Server compares all rows in the selected tables or views. If they contain a large amount of data, the comparison can take a lot of time and even result in running out of disk space. You can simply avoid those by filtering the rows to be compared by applying a WHERE clause to the comparison.

Note:

You can only filter rows if a database is selected as the data source. You can not use WHERE clauses for backups and Scripts folders.

To filter the comparison with a WHERE clause:

1. On the Mapping tab, select the object to apply the filter to.

2. Click the Filter button from the toolbar.

3. In the WHERE Filter window, that opens, type a valid Transact-SQL WHERE clause.

4. If you want to apply the expression to both the Source and the Target, select the Use the same expression for Source and Target checkbox.

5. Click OK to apply the filtering options.

6. The filtered object will be marked with a filter icon in the grid on the Mapping tab.

Mapping Warnings

After objects are mapped for comparison and synchronization, you may encounter warnings on the Mapping tab of the New Data Comparison wizard. They are displayed with the Caution icon instead of a checkbox in the grid.

To read the warning messages, point to the warning icon. All warnings for this object will appear as a hint.

We recommend looking through the warnings before moving to the comparison.

The list of possible warnings:

Warning	Description
Objects to compare have no key column	It means that the custom comparison key is used but is not set. You should mark at least one column pair as a comparison key. For more information see: Selecting the comparison key for each table or view.
No columns to compare	It means, there are no columns with the same name in this pair of tables (views). To compare them you should map the columns manually.

• Previous
• Next