The Data Masker Create/Edit Table Mover Rule Form

This form is used to create and edit Data Masker Table Mover rules. Table Mover rules are designed to copy data from a table in the SQL Server database referenced by the current Rule Controller and insert it into a table in another database. The remote database can be on a different server than that configured in the Rule Controller and can also be of a completely different database type. Currently connections to remote SQL Server and Oracle databases are supported. The example above illustrates a Table Mover rule configured to copy the contents of the DM_CUSTOMER from the local SQL Server database to a remote Oracle database.

This form supports both the editing of existing and the creation of Table Mover Rules. The title text and button labels on the form will change as is appropriate to the create or edit mode. In the example screen shot above, the form is editing an existing Table Mover rule.

The connection information for the remote database is entered in the panel at the top of the Create/Edit Table Mover Rule Form (please see the image above). Details on the usage of this area of the form can be found on the Cross Database Login Panel help page. Please see that page for further information on configuring the remote database connection.

The Target Table

The name of the target table is entered in the Target Table Name field at the top of the Cross Database Login Panel. If the remote database is case sensitive be sure to enter this table name exactly as the remote database requires it. The target table name does not have to be identical to the source table name.

The Columns to Move Panel

Table Mover rules can copy all or some of the columns in the source table to the named table in the remote database. In addition, if the columns in the remote table have different names, then the name of the columns in the target table can be individually specified for each source table column.

The contents of the The Columns to Move Panel cannot be specified until the source table has been selected in the panel on the left hand side. Once the source table has been selected, the drop down box in the first row of the Local Column Name area can be used to choose the column to move. It is a common requirement to move multiple columns and the Add button can be used to add additional columns to the rule. If it is necessary to move all columns in the source table, the Add All button can be used to add all columns from the source table to the Local Column Name area.

By default, the Table Mover rule assumes that the target column name is identical to the source column name. When a new column is added, the Remote Column Name area is populated with the name of the source column. If this is incorrect, then the name of the remote column can be changed by placing the cursor on the remote column name and editing the name to an appropriate value. In the example in the screenshot above, the CUSTOMER_ID column name is identical in both the local and remote tables and has been left un-edited. However the CUSTOMER_FIRSTNAME column in the local table is named FNAME in the remote table and so that value has been edited as appropriate. The names of the remote columns can be changed at any time after the rule has been created.

The Where Clause Panel

Table Mover rules will, by default, copy all of the rows in the source table into the remote target table. If it is necessary to only move a subset of the source table rows to the remote database table a Where Clause can be implemented on the Table Mover rule. Where Clauses are defined using the Where Clause panel on the Where Clause tab. Since the Where Clause will be applied to the source table rows, the SQL of the Where Clause must be defined in a manner appropriate to the source SQL Server database defined in the Rule Controller. The example below illustrates a Where Clause applied to the rule which will only move the rows in the source tables CUSTOMER_ID column which start with the characters IND.

The Data Masker The Table Mover Rule Where Clause Tab

The Error Manager Panel

The Ignore listed errors panel of the Error Manager tab permits the execution of the other rules in the masking set to continue if the specified error occurs. The Ignore Errors panel can contain the error number of all or part of the error message. Table Mover rules can have other rules dependent on them. When the If error is ignored, skip all dependent rule option is active, no dependent rules will be processed if an error is trapped and ignored.

Important Note: The error manager does not trap errors on a row-by-row basis. In other words if it is not possible to insert 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. It will not attempt to move further rows to the remote table. In such a case, the remote table will be in an indeterminate state. In the example below the rule has been configured to ignore any errors relating to the non-existence of the target table. Presumably, in this case, the move of the source rows to the remote table has not been considered essential for some reason.

The Data Masker The Table Mover Rule Form Error Manager Tab

Creating a new Table Mover Rule

Creating or editing a Table Mover rule is a straightforward process. Table Mover rules are created by launching the New Table Mover rule form using the New Rule button located on the bottom of the Rules in Set tab.

As with all Data Masker rules - Table Mover rules execute in the order specified by their Rule Block and Dependency state.

How to Create a New Table Mover Rule