Skip to main content
Skip table of contents

ETL+ Load Upsert Example 2 -- Source without a PK

Upsert example of the less common case where there isn’t a unique key / PK (Primary Key) in the data source / source table. Results can be unexpected.

What this Example Illustrates

  • Upsert technically ‘works’ when the source table doesn’t have a unique key – columns designated by the Key+ checkboxes are unique values in the source table – but the results effectively render Upsert unusable in nearly all cases.

  • When using Upsert with source data from .CSV or Excel files for instance it’s up to the user to ensure that the data has a unique key, that is, the values in a column or columns are always unique in the file or table.

  • References to “unique keys” on the user interface refer only to the source table, not the target table.

  • For processing efficiency, indexes are created on the target table based on the settings of the Key+ checkboxes on the Design page.

  • In most cases the source table will have a unique key. This example is about a Upsert from a source table without a unique key.

ETL+ Configuration for this Example

Load panel from target table in screenshot above

Design page from target table above

Initial Load Upsert

Data Source

Target Table After 2nd Load Upsert

Data from ETL+ Preview page

Data from target table in data warehouse

ETL Log: (4 rows loaded)

2nd Load Upsert

Highwater mark determined from Sequence column in target table before Upsert is 99

Data Source

Target Table After 2nd Load Upsert

Sequence column in last row changed to 100 since last Load.

Data from target table in data warehouse

  1. Deletes all columns with Source_key = 1or 2

  2. Then Inserts all rows with highwater mark >= 99.

ETL Log: (2 rows upserted)

Target Table in Database

Included for those interested. Very few users need to know the internals of the database tables storing the data warehouse.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.