How to filter objects and data

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 comparison options

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

Filter database objects and data using Auto Mapping options

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.

Filter database objects by mask

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.

Filter database objects by Regular Expressions

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 The Filter button.

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.

Exclude data matching certain criteria

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.

WHERE Filter

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Data Compare for SQL Server.
All Features

All features

Get acquainted with the rich features and capabilities of the Data Compare in less than 5 minutes.
Request a demo

Request a demo

If you consider employing the Data Compare for your business, request a demo to see it in action.
Ready to start using dbForge Data Compare for SQL Server?