Post
Ask Question

SQL Merge Statement: Syntax, Usage, and Example

Introduction

The INSERT, DELETE, and UPDATE statements are combined into a single SQL query using the MERGE statement.

SQL MERGE Statement

Based on the outcomes of a JOIN with a source table, the MERGE statement in SQL is used to execute insert, update, and delete operations on a target table. By operating on one table depending on the outcomes of the other, users can synchronize two tables.

Based on predefined key fields, the MERGE statement compares data between a source table and a target table. It makes the proper entries, updates the ones that already exist, and flags or removes records that are no longer in the source.

This assertion offers an adaptable approach to handling data modifications and is frequently employed in situations such as preserving Slowly Changing Dimensions (SCD) in data centers.

Syntax

MERGE INTO targetTable
USING sourceTable
ON mergeCondition
WHEN MATCHED THEN
   UPDATE SET 
	column1 = value1 [, column2 = value2 …]
WHEN NOT MATCHED THEN
   INSERT (column1 [, column2 …])
   VALUES (value1 [, value2 …])
WHEN NOT MATCHED BY
   THEN DELETE;

As an illustration

CREATE TABLE Employee
(
   Id INT PRIMARY KEY,
   Name VARCHAR(100),
   Gender VARCHAR(1),
   [Rank] INT
); 

INSERT INTO Employee
VALUES (1, 'Jaimin', 'M', 93)
, (2, 'Roshni', 'F', 242)
, (3, 'Dwisha', 'F', 345)
, (4, 'Dwiti', 'F', 435)
, (6, 'Ajay', 'M', 234);

CREATE TABLE TempEmployee
(
   Id INT PRIMARY KEY,
   Name VARCHAR(100),
   Gender VARCHAR(1),
   [Rank] INT
);

INSERT INTO TempEmployee
VALUES (1, 'Jaimin', 'M', 91)
, (2, 'Roshni', 'F', 234)
, (3, 'Dwisha', 'F', 321)
, (4, 'Dwiti', 'F', 401)
, (5, 'Jacky', 'M', 786);

SELECT * FROM Employee;
SELECT * FROM TempEmployee;

The target table will then be synchronized with the updated data from the source table using the MERGE command, as shown in the example below.

MERGE Employee AS TARGET
USING TempEmployee AS SOURCE 
ON (TARGET.Id = SOURCE.Id) 

--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.[Name] <> SOURCE.[Name] OR TARGET.[Rank] <> SOURCE.[Rank]
THEN UPDATE SET TARGET.[Name] = SOURCE.[Name], TARGET.[Rank] = SOURCE.[Rank] 

--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET 
THEN INSERT (Id, [Name], Gender, [Rank]) VALUES (SOURCE.Id, SOURCE.[Name], SOURCE.Gender, SOURCE.[Rank])

--When there is a row that exists in target and same record does not exist in source then delete this record target
WHEN NOT MATCHED BY SOURCE 
THEN DELETE 

--$action specifies a column of type nvarchar(10) in the OUTPUT clause that returns 
--one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE' according to the action that was performed on that row
OUTPUT $action, 
DELETED.Id AS TargetId, 
DELETED.[Name] AS TargetName, 
DELETED.Gender AS TargetGender, 
DELETED.[Rank] AS TargetRank, 
INSERTED.Id AS SourceId, 
INSERTED.[Name] AS SourceName, 
INSERTED.Gender AS SourceGender,
INSERTED.[Rank] AS SourceRank; 

SELECT @@ROWCOUNT;

Target

Crucial points for SQL MERGE Statement

  • A semicolon (;) must be used as the statement terminator for the MERGE SQL statement. If the statement terminator is not used when executing a MERGE statement, Error 10713 is raised.
  • The SQL MERGE statement effectively synchronizes data between source and target tables by combining the INSERT, UPDATE, and DELETE operations into a single statement.
  • It can handle several data manipulation operations in one transaction, it offers flexibility when customizing intricate SQL scripts.
  • In situations like maintaining Slowly Changing Dimensions (SCD) in data warehouses, the SQL MERGE statement is frequently utilized.
  • Optimizing the join conditions, filtering the source table for required records, and properly indexing the data can all help the MERGE statement perform at its best.

Amazing things happen when you put yourself out there. Simply jump into this course headfirst and learn everything there is to know about programming.

Happy coding!


Similar Articles