Copy values from a table in a Rule Controller to a different target SQL Server or Oracle database (see Cross Database Login Panel). The target server needn't be included in any rule controller of the masking set.

Conceptually, a cross database table-to-table rule is like running UPDATE <target_table> SET <sync_columns> = <source_values> WHERE <join_columns> = <source_join_value> for each row in the source.

Core concepts

The Target Database

The target database does not have to be located on the same server as the source database and can be of a different type than the source database. Both SQL Server and Oracle target databases are supported.

The Target Table

The name of a target table writable from the login specified in the Cross Database Login Panel. The target table does not have to have the same name or structure as the source table.

The Sync. Columns

Columns to be copied from source to target in the case join columns match. The synchronization columns in the target table can have different names than those of the source table and, if of a different datatype, should be of a datatype suitable for automatic conversion by the target database upon update.

The Join Columns

How to identify rows in the target table that should be updated with the value of the source table. Rows with the same value for join columns are are updated with the contents of the source table's sync columns. The join columns in the target table can have different names than those of the source table.

Advanced concepts

Moving a Subset of the Source Table

It is possible, using the Where Clause feature (applied to the source table rows), of the Cross Database Table-to-Table rule to configure the rule to move only a subset of the rows in the source table to the remote table.

Important Note: It is important to realize that if there are join values in the target table that are not present in the source table, the Cross Database Table-To-Table Synchronization rule will have no effect on those rows. These columns will not be masked because there are no rows on the source side which can update them. In such cases a Cross Database Command rule is usually written to update the columns in the target table with a default value before the synchronization takes place. This ensures that the sensitive data in the target table gets some form of masking even if the subsequent synchronization operation does not update it.

Indexing Join Columns

In general, it is a good idea to always ensure the columns specified for the target side of the join condition are indexed. If they are not indexed, the rule will be much slower as the remote database will need to scan the entire contents of the remote table for each row which needs to be synchronized.

Error Management and Handling

An error inserting the data into the remote table will normally stop execution of the masking set and all further rules. If required, the error can be trapped and the execution of subsequent rules will be possible.

Important Note: The error manager does not trap errors on a row-by-row basis. In other words if it is not possible to update a row (perhaps due to a primary or foreign key violation) the rule will error. If that error is trapped, then the rule will just stop and no further rows will be moved. The rule will not attempt to move further rows to the remote table. In such a case, the remote table will be in an indeterminate state.