Upsert Operation on Parent-Child Tables

When an upsert operation is performed on parent-child tables by using the Data Merger activity, the upsert operation behaves differently in different scenarios.

Consider that the Data Merger schema has parent-child tables and the upsert operation is performed on these tables. The following table summarizes the different scenarios and the results:

Upsert Operation Behavior
Parent Table Child Tables Result
Scenario 1
ADB_OPCODE=2 for a record

Record exists

ADB_OPCODE is blank for all records

Records do not exist

The record in parent table is updated correctly; the same operation (update) is performed on child records.

Non-existing records in child tables are not inserted in the child tables.

Scenario 2
ADB_OPCODE=2 for a record

Record does not exist

ADB_OPCODE is blank for all records

Records exist

The parent record is inserted in the parent table correctly; the same operation (insert) is performed on child records.

“Duplicate records error" occurs when you try to insert existing records in the child tables.

Scenario 3
ADB_OPCODE=2 for a record

Record exists

ADB_OPCODE=1 for all records

Records do not exist

The record in parent table is updated correctly and records are inserted in the child tables correctly.
Scenario 4
ADB_OPCODE=2 for a record

Record does not exist

ADB_OPCODE=2 for all records

Records exist

The record is inserted in the parent table correctly and the child records are updated in child tables correctly.
Note: When working with parent-child tables, the value of ADB_OPCODE in the parent table and the child table must be the same. Or you must set the value of ADB_OPCODE only in the parent table and leave the child table empty.