Use the staging_to_target Element in XML | Teradata Data Mover - 17.11 - About Using staging_to_target Element in XML - Teradata Data Mover

Teradata® Data Mover User Guide

Product
Teradata Data Mover
Release Number
17.11
Release Date
October 2021
Content Type
User Guide
Publication ID
B035-4101-091K
Language
English (United States)

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.

The override fails if you have specified the MERGE value for the staging_to_target element. Data Mover determines that the MERGE method is not successful in copying data, resulting in an error.

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.

Value Description
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.
DELETE_DISTINCT_INSERT

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>