About Using staging_to_target Element in XML - Teradata Data Mover

Teradata Data Mover User Guide

Product
Teradata Data Mover
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-03-29
dita:mapPath
kmo1482331935137.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-4101
lifecycle
previous
Product Category
Analytical Ecosystem

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 data should be copied 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 Recommended because it specifies that Data Mover choose the most efficient way to copy data from the target staging table to the target table.

This is the default value.

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>