Upsert Not Working as Expected

Owned by Cortlandt _
Jun 24, 2023
3 min read

Visit the following page to learn about Upsert functionality and configuration:

ETL+ Table Load Types | Upsert

Common Issues Using Upsert

There are two main scenarios when Upsert doesn’t work as expected:

  1. Records have been deleted from the source table.

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

  2. Upsert is not properly configured.

    1. The Upsert’s configured Primary Key is not the table's correct primary key. This can cause inconsistent Upsert extractions. Please review your source tables' documentation to be sure you have correctly configure the primary key in ETL+ (click the Design icon to configure it).

    2. The column used for the Upsert Column with Last Modified Value filter doesn’t have LastModifiedDate information. See below for an example using the DocumentDate instead of a LastModifiedDateTime column.

Both of 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 center 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.

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 for a date when records are no longer being changed. For instance, 1/1/2022.

  • Change the Load type to Replace

  • Select the Field = a field such as Document Date or Create Date.

  • Add the date information or expression in the right box. In the example below, it will bring all records greater or equal to 2022-01-01. You could also use an SQL function like getdate()-90 (replace all records from the past 90 days).

  • Right-click the table on the center 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.

v2022.08