Data Mover uses target staging tables when performing partial copies or full table copies where the target table already exists. The value of the staging_to_target element specifies how to copy data from a target staging table to a target table, overriding the method Data Mover normally uses to copy data from staging to the target table. The Data Mover method uses MERGE or DELETE together with INSERT/SELECT, depending on factors such as key columns, database version, and so forth.
Use the staging_to_target element if you do not have UPDATE permission required by the MERGE statement. However, use the parameter carefully as Data Mover automatically chooses the most efficient way of copying data.
The staging_to_target element is optional and can only be specified in the XML file, not as a parameter to the command-line interface.
The staging_to_target element is under the key_columns element in the XML file. The following table lists the valid values for the staging_to_target element.
|NOT_SPECIFIED||(Default) Recommended because it specifies that Data Mover choose the most efficient way to copy data from the target staging table to the target table.|
|MERGE||Specifies the use of the MERGE statement to copy data. Data Mover verifies whether the MERGE statement can be used to copy data. An error results if the MERGE statement cannot be used. Note that the MERGE statement cannot be used to copy multiset tables.|
|DELETE_INSERT||Specifies rows from the target table are deleted with the DELETE statement, then copied from the staging table to the target table with the INSERT/SELECT statement.
Rows to be deleted depend on the job. With a partial table copy, only the rows that match the SQL query are deleted. With a full table copy, all rows are deleted.
|INSERT_ONLY||Specifies no rows are deleted from the target table. Instead, the INSERT/SELECT statement copies rows from the staging table to the target table.|
Specifies rows from the target table are deleted with the DELETE DISTINCT SELECT statement, then copied from the staging table to the target table with the INSERT/SELECT statement.
This method is used by partial copy only, and only when explicitly specified.
This method improves delete performance when key columns are non-unique keys.
Because functionality of the staging_to_target element is valid for individual tables, specify the table to which the staging_to_target feature applies, as in the following example.
<database selection="unselected"> <name>srcDatabase</name> <table selection="included"> <name>srcTable</name> <sql_where_clause> <![CDATA[WHERE colA > 100]]> </sql_where_clause> <key_columns> <key_column>colA</key_column> </key_columns> <staging_to_target>insert_only</staging_to_target> </table> </database>