A supplier sends its full catalog every Monday. In week one, 8,000 products load cleanly. In week two, the same file arrives with 40 price changes and 12 new items. If the sync simply adds every row, the catalog now holds over 16,000 products, and half of them are duplicates. An upsert prevents this. It is a single operation that updates a record if it already exists and inserts it if it is new.
The word combines “update” and “insert.” A match key, such as a SKU, tells the system which records already exist.
For each incoming record, an upsert asks one question: is this already here? If yes, it updates the existing record with the new values. If no, it creates a new record.
Here is how the Monday catalog file would be handled:
| Incoming record | Already in the catalog? | Upsert result |
| SKU-1001 with a new price | Yes | Price updated |
| SKU-2040 | No | New product created |
| SKU-1002 with no changes | Yes | Record stays the same |
The outcome is predictable. Running the same upsert twice with the same data gives the same result as running it once. Engineers call this idempotent. For business teams, it means a retried job or a schedule that fires twice will not create duplicates.
All three operations write data. They behave differently when a record already exists.
| Operation | If the record exists | If the record is new | Risk when a job runs again |
| Insert | Adds a second copy, or fails | Creates the record | Duplicates |
| Update | Changes the record | Does nothing | New records are missed |
| Upsert | Changes the record | Creates the record | None, when the key is unique |
The match key is the field an upsert uses to compare records. Choosing it well matters more than anything else in the setup.
A good match key has three qualities. It is unique, so two records never share the same value. It is always present, because a record with an empty key cannot be matched. And it is shared by both systems, in the same format.
Common match keys include SKUs, product codes, email addresses, order numbers and external IDs copied over from another platform.
The third quality causes the most trouble in content and product stacks. A PIM may treat the SKU as its main key. A DAM may use an asset ID and keep the SKU as optional metadata. If the SKU is missing or written differently, the upsert cannot find the right record. We covered this in How Integration Layers Improve AI Data Hygiene.
Upserts appear wherever data is synced on a schedule or after an event.
Most databases and business apps support upserts in their own way. PostgreSQL uses an INSERT command with an ON CONFLICT clause, and Salesforce offers an upsert operation that matches on an External ID field. The idea is the same in each case.
Most upsert problems start with the data.
The OneTeg Data Store is built into the platform, so flows can keep tables in sync without a separate database. Upserts are central to how those tables stay current.
First, you choose a match key. In the field designer, you mark a field such as SKU as a unique key. If you do not name a key, the table’s primary field is used.
Next, a flow step uses the Upsert rows action of the Data Store connector. It writes a batch of up to 200 rows in one call, creating new rows and updating existing ones by key. The response reports how many rows were created and updated. The connector needs no connection setup.
OneTeg checks the whole batch before writing anything. If a row has no value for the key field, the step is refused with a clear message, and nothing is written. Sending the same batch twice gives the same result, so reruns and duplicate triggers add no copies.
CSV imports follow the same idea. When you reload a file, choose Update rows matching a key field instead of Append every row. A dry run checks a sample before anything changes. Files up to 2 GB are supported, and about 1 million rows load in roughly 13 seconds.
A typical product sync flow has three steps:
Without upsert, the same flow would need a lookup, a condition and separate insert and update steps for every record. The Data Store user manual covers each action. Batch Processing iPaaS for Scalable Workflows and the Product Data Synchronization use case show how these syncs run at scale.
An upsert writes a record once. If a record with the same key already exists, it is updated. If not, a new record is created.
An insert always creates a new record. An upsert checks the match key first and updates the existing record when there is one, so it avoids duplicates.
No. A standard upsert only creates and updates records. Records removed at the source need a separate delete step.
The system cannot tell which record to update. Most databases and integration tools refuse the upsert until the key field is marked unique.
MERGE is the SQL statement some databases, such as SQL Server and Oracle, use for upserts. It can also delete rows under set conditions, which a basic upsert does not do.
Upserts keep synced data clean. They hold one record per key, handle new and existing records in one step and make retries safe. Pick a unique, shared match key, normalize it before syncing and let the upsert do the rest.
Contact us to schedule a OneTeg demo and see how the OneTeg Data Store keeps your product, asset and reference data in sync.