Snowflake
Synchronize Tables

Snowflake MERGE: How to Synchronize Data Across Tables

The MERGE function is a SQL command used to synchronize two tables or datasets. It can insert new records, update existing ones, or delete records based on the data in a second table. Think of it as an all-in-one tool for ensuring your main table is up to date with the latest changes.

What it does

MERGE looks at two sets of data: one is your main dataset (the target), and the other is usually a newer dataset (the source) that contains updates, new additions, or deletions. Based on rules you define, it updates the target table by:

  • Inserting new rows found in the source but not in the target.
  • Updating existing rows in the target where corresponding matches are found in the source.
  • Deleting rows in the target if specified conditions are met.

Syntax

Here’s the basic way you write a MERGE statement:


_10
MERGE INTO <target_table> USING <source_table>
_10
ON <join_condition>
_10
WHEN MATCHED THEN
_10
UPDATE SET <column> = <expression>
_10
DELETE WHERE <condition>
_10
WHEN NOT MATCHED THEN
_10
INSERT (<column_names>) VALUES (<values>)

where:

  • <target_table>: The table you want to update.
  • <source_table>: The table that contains new or updated data.
  • <join_condition>: The condition used to match rows between the two tables.
  • WHEN MATCHED and WHEN NOT MATCHED: Define what actions to take when rows match or do not match the join condition.

Examples

Sync two tables

Imagine you have a table of registered conference attendees (target_table) and a new sign-up list (source_table). You want to make sure the attendee list is updated with any new sign-ups, correct existing attendee details, or remove cancellations:

Input

Table: conference_attendees

emailnamecanceled
john.doe@example.comJohn DoeFALSE
jane.smith@example.comJane SmithTRUE

Table: new_signups

emailnamecanceled
john.doe@example.comJonathan DoeFALSE
alice.jones@example.comAlice JonesFALSE

Snowflake SQL Query


_10
MERGE INTO conference_attendees USING new_signups
_10
ON conference_attendees.email = new_signups.email
_10
WHEN MATCHED AND new_signups.canceled = FALSE THEN
_10
UPDATE SET name = new_signups.name
_10
WHEN MATCHED AND new_signups.canceled = TRUE THEN
_10
DELETE
_10
WHEN NOT MATCHED THEN
_10
INSERT (email, name, canceled) VALUES (new_signups.email, new_signups.name, new_signups.canceled);

Output

Table: conference_attendees

emailnamecanceled
alice.jones@example.comAlice JonesFALSE
john.doe@example.comJonathan DoeFALSE
jane.smith@example.comJane SmithTRUE

Explanation

  • Update Existing Attendees: The MERGE command updates the name of John Doe to Jonathan Doe based on the updated information in new_signups.
  • Delete Canceled Registrations: If any attendee in new_signups is marked as canceled (no such case in this example), their record would be deleted from conference_attendees.
  • Add New Attendees: Alice Jones, a new attendee from the new_signups table, is added to the conference_attendees.
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free