"When there are no expressions involved and at most one conversion required before
loading the data into the target table, the data conversion takes place in the acquisition
phase and the converted data is loaded into the worktable. This means that any errors with the data or with the conversion will be detected in the acquisition phase."
If there are expressions involved, which doesn't make conversion in acquisition phase, then will ET table be loaded with any rows at the end of acquisition phase? If so, what kind of errors!
Does "IGNORE DUPLICATE ROWS" applicable to constraint violations or does we have error treatment options only to the entire DUPLICATE row. Say, if I have a check constraint on a column abc to have only values 1,2,3. If I update the column abc in the target that has a value of 3 with 55, will the record in the source be placed into UV table?
Also, does "IGNORE DUPLICATE ROWS" have effect on unique constraint violations (USI, primary key)? Will the records that violate USI, primary key be logged into the UV table or ignored!
MARK/IGNORE DUPLICATE ROWS has no effect if the table is a multiset table
(which allows duplicate rows).
IGNORE DUPLICATE ROWS has no effect if the table has a unique primary
index. Since a duplicate row implies a uniqueness violation in this case, the row
is logged to the uniqueness violation table.
In the case of an upsert operation, both the insert and update portions must fail
for an error to be recorded. In this case, the mark rows for the missing update
operations then have nulls for the target table columns.
If either INSERT or UPDATE with DUPLICATE is specified, then the MARK or
IGNORE specification applies to both insert and update operations.
Similarly, if either UPDATE or DELETE with MISSING is not specified, then the
MARK or IGNORE specification applies to both update and delete operations.
Note: MARK is the default for all actions except MISSING UPDATE for an
You can have a try on your specific test case, and let us know if you have any question on the utility behavior. Thanks!