Applies to
ApexSQL Data Diff and ApexSQL Data Diff for MySQL
Summary
This article explains how to use the Row filter option in ApexSQL Data Diff and ApexSQL Data Diff for MySQL to limit the comparison in SQL Server to a specific subset of rows.
Description
The Row filter option allows filtering database rows and including or excluding some of them from the SQL and MySQL Server comparison and synchronization process, depending on the defined condition.
In the New project window, set the data sources (source and destination database), expand the Advanced options tab, and select the Object filter tab:
After selecting the Object filter a list of all objects in the databases will be presented. Select the Row filter cell of the desired object, and click the ellipse (…) button:
|
Quick tip: Excluding unneeded rows from the SQL and MySQL Server comparison and synchronization, can speed up the process |
Enter the query condition in the Row filter window:
The entered condition will filter the Employee table comparing only rows that match the condition.
|
Quick tip: Any condition that can be used in the WHERE clause of a T-SQL query, and other related clauses (GROUP BY, ORDER BY, HAVING, etc.) can be used as a Row filter condition as well. |
The apex filter condition can be the same for both databases, or it can be different. Uncheck the Use the same filter condition for both objects option to enter different conditions for source and destination database:
Using the Row filter feature
In the following example we will show the usage of the Row filter option with the same condition for a source and destination databases. The condition in this example will filter the ContactTypes table by ContactTypeID column values from 5 to 20:
ContactTypeID>=5 and ContactTypeID<=20
Click the Apply button, at the bottom of this window, to get the Row count, which is the number of the rows that match the previously mentioned condition.
ApexSQL Data Diff will add this condition as the WHERE condition into the T-SQL synchronization script and update only rows that match the condition:
UPDATE [Person].[ContactType] SET [ModifiedDate]='19980601 00:00:00.000' WHERE [ContactTypeID]=5 AND (ContactTypeID>=5 and ContactTypeID<=20) … UPDATE [Person].[ContactType] SET [ModifiedDate]='19980601 00:00:00.000' WHERE [ContactTypeID]=20 AND (ContactTypeID>=5 and ContactTypeID<=20)
To apply a different condition to a destination database, uncheck the Use the same filter condition for both objects option, and enter the WHERE clauses for both databases.
For example, if there are two databases with the same credit card, but with different dates for using the credit card, and those uses need to be compared by the ModifiedDate column, a different WHERE condition can be specified in order to filter rows for comparison.
The condition in this example filters the ModifiedDate column from date 2002-07-01 to 2003-07-01 for the source database, and ModifiedDate column from date 2006-07-01 to 2007-07-01 for the destination database.
The condition for this example is as follows:
For the source database: ModifiedDate>=’2002-07-01′ and ModifiedDate<='2003-07-01'
For the destination database: ModifiedDate>=’2007-07-01′ and ModifiedDate<='2008-07-01'
After refreshing the row count, the same number of rows is in both objects. It means that the same products with modified date costs are filtered for comparison and synchronization.
A T-SQL synchronization script for this condition will be:
DELETE TOP(1) FROM [Production].[ProductCostHistory] WHERE [ModifiedDate]='20070617 00:00:00.000' AND (ModifiedDate>='2002-07-01' and ModifiedDate<='2003-07-01') … DELETE TOP(1) FROM [Production].[ProductCostHistory] WHERE [ModifiedDate]='20070630 00:00:00.000' AND (ModifiedDate>='2007-07-01' and ModifiedDate<='2008-07-01')
