Subscription Replication Criteria

By default, Data Replicator replicates all rows from the source table to the target table or stored procedure. When you define a subscription or view the properties of an existing subscription, you can specify a SQL SELECT statement that determine which data in the source table is replicated. The SELECT statement allows you to filter the source data so that only data that meets the criteria is replicated to the destination table.. You also can enter SQL statements to group or reorder rows for a snapshot subscription. See the section below for additional considerations for applying criteria to incremental replication subscriptions.

To replicate only selected source rows, type a valid SQL WHERE clause in the edit box. For example, to replicate only source rows where the ZIP column contains values greater than 89999 and less than 95000, you would enter WHERE ZIP>89999 AND ZIP<95000 in the criteria edit box.

For snapshot subscriptions you can enter SQL clauses using GROUP BY and HAVING in the criteria edit box. For example, to have the destination table display rows grouped by ZIP column value, you would enter GROUP BY ZIP as the criterion.

After you specify the criteria, click the Verify Criteria button to help ensure the SQL statement performs a valid operation. Data Replicator evaluates the criteria and either shows how many source rows would be selected based on that criteria, or displays information if the criteria is not valid.

You cannot specify replication criteria when you create multiple subscriptions at one time, although you can later edit individual subscriptions to add criteria that determine which source data the subscription replicates.

Specifying Replication Criteria for Incremental Subscriptions

The criteria you specify for an incremental subscription not only applies to the baseline replication but also to changes that are made to the source table. When the source table changes, the changes are evaluated against the criteria to determine whether to replicate the change to the target table. Evaluating criteria for incremental replication operations is most successful and consistent when the criteria uses only constant values as selection criteria.

The nature of applying changes incrementally imposes additional restrictions for specifying replication criteria.

• Do not specify GROUP BY, ORDER BY, or HAVING clauses in the criteria for incremental replication operations.

• The replication criteria cannot be evaluated against LOB values, as LOB values are not stored in the change data tables.

• Avoid references to other tables. Changes to a table that is used by another table are not automatically applied to the dependent table.

• Avoid using dynamic system values, such as the current date. This can produce unexpected results, such that records that correspond to the current date are replicated initially, and then only the changes to those records are replicated to the destination.

• Be aware that the criteria you specify may require Data Replicator to change the type of operation that is sent to the destination table. For example, if the change involves an UPDATE operation to a value that previously did not meet the criteria, Data Replicator sends the operation as an INSERT instead of an UPDATE. If an INSERT meets the criteria and a subsequent UPDATE does not, the UDPATE is changed to DELETE so the value will be deleted from the target table.

Run a new baseline replication to re-evaluate the source table against the criteria if you need to ensure the destination table reflects all the data that meets the criteria.

In some cases (e.g. when the source of an incremental replication is SQL Server. MySQL, or another DBMS that allows mixed or lower case column names), you may need to place double quotes around the column name.  e.g. WHERE "ZIP">89999 AND "ZIP"<95000.