Merge in SQL Server

The MERGE command synchronizes two tables by adding, removing, and modifying target table rows based on the source table’s join condition. In other words, when performing INSERT, UPDATE, and DELETE operations on a target table by matching the rows from the source table, MERGE is frequently utilized throughout the ETL process.

SQL Server supports the MERGE command that allows analysts and developers to conduct DML actions in a single statement, allowing them to perform INSERT, UPDATE, or DELETE if the record exists or not. If the operation succeeds, the modifications will be committed automatically.

Syntax:
MERGE INTO <target table> AS T USING <source expression/table> AS S
ON <boolean expression1>
WHEN MATCHED [AND <boolean expression2>] THEN UPDATE SET <set clause list>
WHEN MATCHED [AND <boolean expression3>] THEN DELETE
WHEN NOT MATCHED [AND <boolean expression4>] THEN INSERT VALUES<value list>

Use Case:

CREATE TABLE SalesRepository(
ProductID VARCHAR(10),
ProductName VARCHAR(100),
Model VARCHAR(30),
QtySold INT,
SoldDate VARCHAR(30))

CREATE TABLE NewSales(
ProductID VARCHAR(10),
ProductName VARCHAR(100),
Model VARCHAR(30),
QtySold INT,
SoldDate VARCHAR(30))

INSERT INTO SalesRepository VALUES
(‘P1001’, ‘Hisense 65inch’, ’65U7WF’,5, ‘20210811’),
(‘P1002’, ‘Hisense 58inch’, ’58A7100F’,3, ‘20210811’),
(‘P1003’, ‘Hisense 55inch’, ’55A7100F’, 2,’20210811′),
(‘P1004’, ‘Hisense 55inch’, ’55U7WF’, 4,’20210811′),
(‘P1005’, ‘Hisense 43inch’, ’43A6000F’, 3,’20210812′),
(‘P1006’, ‘Hisense 50inch’, ’50A6GE’, 0,’20210812′),
(‘P1007’, ‘Hisense 50inch’, ’50A7100F’, 0,’20210813′),
(‘P1008’, ‘Hisense 65inch’, ’65A6GE’, 2,’20210813′),
(‘P1009’, ‘Hisense 43inch’, ’43B7100UW’,2, ‘20210813’)

INSERT INTO NewSales VALUES
(‘P1002’, ‘Hisense 58inch’, ’58A7100F’,3, ‘20210811’),
(‘P1003’, ‘Hisense 55inch’, ’55A7100F’, NULL,’20210811′),
(‘P1006’, ‘Hisense 50inch’, ’50A6GE’, 1,’20210812′),
(‘P1007’, ‘Hisense 50inch’, ’50A7100F’, 2,’20210813′),
(‘P1010’, ‘Hisense 50inch’,’50A7120FS’, 12,’20210814′)

Since “NewSales” contains accurate information, the table “SalesRepository” has to be updated depending on the values supplied in “NewSales.” If no sales occurred (QtySold in the “NewSales” table is null), the record should be deleted from the “SalesRepository.” The following must be accomplished.

• The values for QtySold in table “SalesRepository” for the ProductID # P1002, P1002, P1004, P1005, P1008 and P1009 should not be changed.
• The value for QtySold in table “SalesRepository” for the ProductID # P1003 should be deleted.
• The value for QtySold in table “SalesRepository” for the ProductID # P1006 should be updated from 0 to 1.
• The value for QtySold in table “SalesRepository” for the ProductID # P1007 should be updated from 0 to 2.
• The row with ProductID # P1010 in the table “SalesRepository” should be inserted.

Merge Query:
MERGE INTO SalesRepository AS SR
USING NewSales AS NS
ON SR.ProductID = NS.ProductID and SR.SoldDate = NS.SoldDate
WHEN MATCHED AND (SR.QtySold != NS.QtySold AND SR.QtySold=0) THEN UPDATE SET QtySold = NS.QtySold
WHEN MATCHED AND NS.QtySold IS NULL THEN DELETE
WHEN NOT MATCHED THEN INSERT VALUES (NS.ProductID, NS.ProductName, Model, QtySold, SoldDate);

Result:

Please note that there should be a semi-colon at the end of the merge statement. Otherwise, you’ll receive the following error message.

Msg 10713, Level 15, State 1, Line 39
A MERGE statement must be terminated by a semi-colon (;).

Hope you find this article helpful.

Please subscribe for more interesting updates.