In some circumstances, Data Mover uses MERGE instead of INSERT-SELECT when copying data from a staging database to a target database. The result is better performance.
The following conditions must be met for Data Mover when using MERGE:
- A partial table copy must be specified.
- The table cannot be a multiset table.
- The table must contain one or more primary indexes.
- All indexes must be specified as key columns.
- For PPI tables, all partitioned columns must be specified as key columns.
- The primary index on the target table does not contain an identity column.
If these conditions are not met, Data Mover uses DELETE and INSERT-SELECT to transfer data.
For PPI tables, indexes and partitioned columns must be specified as key columns. Specifying only the indexes as key columns results in a failed job execution.