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.
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.
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.
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.
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.