Upsert Data to a Target Table
This quickstart article describes how to use INSERT and MERGE to update a row or insert a new row in your target table.
Upserting is a term we use to describe row transformation. When you create a job to upsert data, the job performs one of two functions:
Inserts a row in your target table with new data
Updates an existing row with the new data
Upsert using INSERT
To upsert data, your target table should have at least one primary key to match the rows between your source and target tables.
If your target table is an Upsolver-managed table, you can define a primary key as follows:
You can then insert your data into your target table. To do this, run the following job:
This job assumes you have your source data in a staging table my_catalog.my_schema.source_table. Furthermore, it assumes this table contains a primary key column that matches the primary key column of your target table.
Having a primary key in the target table means that if a new row arrives that shares a primary key value with an existing row, the old row is updated; otherwise, Upsolver inserts the new row into the table.
Finally, use a SELECT
statement to apply transformations to your data:
Query your data with the following statement.
Upsert using MERGE
As with the INSERT command, to upsert your data your target table should have at least one primary key to match the rows between your source and target tables.
Also as before, if your target table is an Upsolver-managed table, you can define a primary key as follows:
You can then merge your data into your target table by running the following job:
Finally, apply transformations to your data using a SELECT
statement.
Query your data with the following statement.
Last updated