Post
Ask Question

Conditional INSERT, UPDATE, DELETE with MERGE Query

Introduction

In SQL Server, you can use the MERGE statement to perform conditional INSERT, UPDATE, or DELETE operations in a single query. The MERGE statement is often used for synchronizing data between two tables or performing upserts (INSERT or UPDATE, depending on whether a matching row exists). Here's an example of how you can use the MERGE statement with multiple examples for each operation (INSERT, UPDATE, DELETE):

Assume you have two tables: TargetTable and SourceTable. We'll use these tables for our examples.

INSERT

Suppose you want to insert rows from SourceTable into TargetTable if they don't already exist in the target table.

MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.ID = Source.ID
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name)
    VALUES (Source.ID, Source.Name);

This SQL code will insert rows from SourceTable into TargetTable where the ID doesn't match.

UPDATE

Suppose you want to update existing rows in TargetTable with data from SourceTable. When there's a match.

MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.ID = Source.ID
WHEN MATCHED THEN
    UPDATE SET Target.Name = Source.Name;

This code updates the Name column in TargetTable If there's a matching ID in SourceTable.

DELETE

Suppose you want to delete rows from TargetTable If they don't exist in SourceTable.

MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.ID = Source.ID
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

This code will delete rows from TargetTable where the ID doesn't match any in SourceTable.

Now, let's see how you can execute these MERGE statements with multiple examples.

-- Example 1: Insert
INSERT INTO SourceTable (ID, Name)
VALUES (1, 'John'), (3, 'Alice');

-- Example 2: Update
UPDATE SourceTable
SET Name = 'Bob'
WHERE ID = 2;

-- Example 3: Delete
DELETE FROM SourceTable
WHERE ID = 4;

-- Execute MERGE for all operations
MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.ID = Source.ID
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name)
    VALUES (Source.ID, Source.Name)
WHEN MATCHED THEN
    UPDATE SET Target.Name = Source.Name
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

In this example, we first perform individual INSERT, UPDATE, and DELETE operations on the SourceTable. Then, we execute the MERGE statement to synchronize the TargetTable with the SourceTable using a single query. The MERGE statement handles all three operations based on the specified conditions.


Similar Articles