How to filter objects and data

Last modified: August 8, 2024

There are several ways to filter objects and data for comparison and synchronization in dbForge Data Compare for SQL Server.

Comparison Options: You can filter database objects and data based on specific comparison options.

Mapping: You can specify how objects in the source database correspond to objects in the target database.

WHERE filter: You can define conditions that restrict the data rows to be included in the comparison and synchronization.

Filter database objects and data using comparison options

You can filter database objects to be compared and synchronized in the New Data Comparison wizard during the configuration of data comparison options.

You can use the following options to include or exclude database objects from comparison and, consequently, synchronization.

• Compare tables

• Compare views

• Compare memory-optimized tables

• Compare tables with COLUMNSTORE indexes

• Compare history tables for temporal tables

To include or exclude specific objects from comparison and synchronization, you can simply select or clear the corresponding checkboxes. For instance, if you clear the Compare views checkbox, dbForge Data Compare will not compare and synchronize views. Similarly, you can use the Compare tables option. Both options can be used as a table filter and/or view filter, refining your selection of database objects to be compared.

Filter database objects and data using auto mapping options

In the Auto Mapping section of the comparison options, you can filter database objects to be included in comparison and synchronizations by mask. This feature is particularly valuable when you want to selectively include tables in your database comparison/synchronization process without having to list each table individually. For this, you can use the following options:

• Include tables or views by mask

• Exclude tables or views by mask

• Ignore columns by mask

To include\exclude database objects from the comparison and deployment by mask:

1. Select the required option.

2. Click … to open the Edit Mask dialog.

3. Specify the mask and click OK.

For example, if you select Include tables or views by mask and provide jobs, Department masks in the Edit Mask dialog, all tables and views with such names as dept.Jobs, dept.Tmp_Jobs, Tmp_Jobs_History, sales.Departments, dept.tmp_departments, dept.tm_departmeтts2 will be included in the comparison and deployment.

Use Regular Expressions (Regex) for advanced data and objects filtering

In dbForge Data Compare for SQL Server, you can also filter database objects to be included in data comparison and synchronization using regular expressions.

A regular expression is a string that describes patterns that can be used to match and locate database objects.

To use regex to filter database objects, in the Edit Mask dialog, select Use Regular Expression. Then click the arrow to display the list of supported required expressions and select the required one. Finally, define a regex pattern to specify the objects you want to include or exclude from the comparison and click OK.

WHERE filter

Data Compare for SQL Server allows you to exclude specific rows of data from comparison and synchronization using advanced filter rules. To create such a data filter rule:

1. In the New Data Comparison wizard, go to the Mapping tab.

2. Select the table you want to apply the filter to, and then click .

3. In the WHERE filter dialog that opens, specify the WHERE clause expression to exclude the rows that don’t match it.

4. Optional: Click Validate to check the expression you’ve provided.

5. Click OK to apply the filter.

Note

When entering a WHERE clause, you can get a list of table columns by calling the context menu. For this, right-click a left field and hover over Insert Column.