Data Operation - Upsert

About

A upsert operation is an operation against the data that will:

• try to insert a record
• and update it if it already exists (when a unique constraint is violated)

The data upsert operation supports the upsert operation.

Requirements

• The source data resource should be in a tabular format and have data for all constrained columns of the target table.
• The target data resource should have a unique constraint (a primary or a unique key).

FAQ

Which unique constraint (primary/unique key) do you choose as update identifier ?

An upsert can only do an update on one unique constraint identifier.

This is not possible to specify two unique constraints and therefore Tabulify needs to choose one.

If you have multiple target unique constraints matched by the source columns, the process takes as unique constraint:

• the first target unique key found in the source columns.
• and if not present the target primary key.

Note that when a unique key is chosen, the other unique constraint (primary and unique key) are discarded in the update.

Why an upsert does not load all my rows

An upsert will load (insert/update) the rows on the unique key, not on the primary key.

So the number of rows loaded (inserted/updated) is the number of distinct value in the unique key.

If you have 100 distinct value in your primary key but 50 distinct value in your unique key, you will load only 50 records.