Upsert Not Working as Expected

Troubleshooting Load Upsert.

Common Issues with Upsert

1. Records have been deleted from the source table.

Load Upsert captures new and modified records, but it can’t capture deleted records.

2. Key+ columns are not properly configured on the Design page.

The column(s) designated as Key+ on the target table’s Design page must correspond the the source tables primary key column(s). Review the source tables' documentation to be sure you have correctly designated the Key+ column(s).

Example of **Key+** column corresponding to the source table’s primary key (**Src PK**).

3. Column with Last Modified Value doesn’t have right high watermark field.

Example: Should have designated `LastModifiedDateTime` instead of `H_DocDate` as the high watermark.

Solutions: These situations can be fixed with the approaches below:

Fixing Small Tables

When working with tables that can be quickly extracted in full: re-load all data once and switch back to Upsert:

Change the load type to Load All
Right-click the table on the Target Objects panel → Load Now.
Change the Load type back to Upsert
Select a Column with Last Modified Value → Confirm → Save
Column with Last Modified Value = LastModifiedDateTime
Moving forward, only new and modified records will be loaded.

Fixing Large Tables

Important: Before experimenting with ETL+ settings on large tables, you might want to test the changes with smaller tables first. It’s easier and faster to test concepts with small data sets.

When working with tables that can take quite a while to extracted in full, we recommend replacing records in the recent history (for instance, records of closed fiscal years no longer can change) and then switching back to Upsert:

First, find or decide on the date when older records are no longer being changed. For instance, 1/1/2022.
Change the Load type to Replace.
Select a Field option such as Document Date or Create Date.
Add the date information or expression in the data entry box on the right. Examples:
- Replace records greater than or equal to 2022-01-01.
- An SQL function like getdate()-90 would replace all records from the past 90 days.
Right-click the table on the Target Objects panel -> Load Now.
Change the Load type back to Upsert
Select the LastModifiedDateTime field, Confirm and Save

Moving forward, only new and modified records will be loaded.

Related Pages

ETL+ Table Load Types | Upsert page has more info on Load Upsert functionality and configuration.

v2022.08 ✅