> For the complete documentation index, see [llms.txt](https://upsolver.gitbook.io/content/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://upsolver.gitbook.io/content/quickstarts-1/jobs/transformation/updating-data/upsert-data-to-a-target-table.md).

# Upsert Data to a 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:

1. Inserts a row in your target table with new data
2. Updates an existing row with the new data

You can us the [`INSERT`](/content/reference-1/sql-commands/jobs/create-job/transformation/insert.md) or [`MERGE`](/content/reference-1/sql-commands/jobs/create-job/transformation/merge.md) functions to upsert data. Examples of both are below.

## 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:

```sql
CREATE TABLE default_glue_catalog.upsolver_samples.orders_upsert_with_insert
    (customer_email string)
PRIMARY KEY customer_email;
```

You can then insert your data into your target table.  To do this, run the following job:

```sql
CREATE JOB insert_orders_upsert
    START_FROM = BEGINNING
    ADD_MISSING_COLUMNS = TRUE
    RUN_INTERVAL = 1 MINUTE
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_upsert_with_insert 
    MAP_COLUMNS_BY_NAME
```

{% hint style="info" %}
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.
{% endhint %}

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:

```sql
SELECT customer.email AS customer_email, 
       COUNT(DISTINCT orderid) AS number_of_orders,
       SUM(nettotal) AS total_sales,
       MIN(orderdate) AS first_purchase,
       MAX(orderdate) AS last_purchase
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
GROUP BY 1
HAVING COUNT(DISTINCT orderid::string) > 1;
```

Query your data with the following statement.

```sql
SELECT * 
FROM default_glue_catalog.upsolver_samples.orders_upsert_with_insert 
LIMIT 10;
```

## 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:

```sql
CREATE TABLE default_glue_catalog.upsolver_samples.orders_upsert_with_merge
    (customer_email string)
PRIMARY KEY customer_email;
```

You can then merge your data into your target table by running the following job:

```sql
CREATE JOB merge_orders_upsert
    START_FROM = BEGINNING
    ADD_MISSING_COLUMNS = TRUE
    RUN_INTERVAL = 1 MINUTE
AS MERGE INTO 
    default_glue_catalog.upsolver_samples.orders_upsert_with_merge AS target
```

Finally, apply transformations to your data using a `SELECT` statement.

```sql
USING (
	SELECT customer.email AS customer_email, 
		COUNT(DISTINCT orderid) AS number_of_orders,
		SUM(nettotal) AS total_sales,
	   	MIN(orderdate) AS first_purchase,
		MAX(orderdate) AS last_purchase
	FROM default_glue_catalog.upsolver_samples.orders_raw_data
	WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
	GROUP BY 1
	HAVING COUNT(DISTINCT orderid::string) > 1
) source
ON (target.customer_email = source.customer_email)
-- Update if primary keys match
WHEN MATCHED THEN REPLACE 
-- Insert if primary key is unique (new record)
WHEN NOT MATCHED THEN INSERT MAP_COLUMNS_BY_NAME; 
```

Query your data with the following statement.

```sql
SELECT * 
FROM default_glue_catalog.upsolver_samples.orders_upsert_with_merge 
LIMIT 10;
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://upsolver.gitbook.io/content/quickstarts-1/jobs/transformation/updating-data/upsert-data-to-a-target-table.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.