Merge Query In SQL Server

Here, we will learn about how to use merge query in sql.

We need to perform INSERT, UPDATE, and DELETE operations on a target table by matching with records from the source table. For example, a Stock table has information about the stocks and you need to sync this table with the latest information about the stocks from the source table. We would need to write separate DML commands (INSERT statements, UPDATE statements and DELETE statements) to refresh the target table with an updated stock list in your SQL database. In these tips, we will walk through how to use the MERGE statement.

The MERGE operation basically merged data from a source table to a target table based on a condition that you specify and if the data from the source already exists in the target or not. The SQL command combines the sequence of conditional INSERT, UPDATE, and DELETE commands in a single atomic statement, depending on the existence of a record.

Here is the new MERGE syntax:

MERGE <target_table> [AS TARGET]
USING <table_source> [AS SOURCE]
ON <search_condition>
[WHEN MATCHED 
   THEN <merge_matched> ]
[WHEN NOT MATCHED [BY TARGET]
   THEN <merge_not_matched> ]
[WHEN NOT MATCHED BY SOURCE
   THEN <merge_matched> ];

Now, let see an example of the MERGE query:

Create 2 tables for target and source,

CREATE TABLE Stock
(
   StockId INT PRIMARY KEY,
   StockName VARCHAR(100),
   Rate DECIMAL(18,2)
) 
GO

INSERT INTO Stock
VALUES
   (1, 'Tea', 10.00),
   (2, 'Coffee', 20.00),
   (3, 'Chips', 30.00),
   (4, 'Biscuit', 40.00)
GO


CREATE TABLE UpdateStock
(
   StockId INT PRIMARY KEY,
   StockName VARCHAR(100),
   Rate DECIMAL(18,2)
) 
GO

INSERT INTO UpdateStock
VALUES
   (1, 'Tea', 12.00),
   (2, 'Coffee', 25.00),
   (4, 'Biscuit', 40.00),
   (5, 'Cold Drinks', 30.00),
   (6, 'Chocolate', 80.00)
GO

After the run of the above script, we create 2 tables and the data looks like below:

Note: stock table is our TARGET table and UpdateStock is the SOURCE table.

Next, I will use the MERGE command to synchronize the target table with the refreshed data coming from the source table in the following example:

MERGE Stock AS TARGET
USING UpdateStock AS SOURCE
ON (TARGET.StockId = SOURCE.StockId)
WHEN MATCHED AND TARGET.StockName <> SOURCE.StockName
                 OR TARGET.Rate <> SOURCE.Rate THEN
    UPDATE SET TARGET.StockName = SOURCE.StockName,
               TARGET.Rate = SOURCE.Rate
WHEN NOT MATCHED BY TARGET THEN
    INSERT
    (
        StockId,
        StockName,
        Rate
    )
    VALUES
    (SOURCE.StockId, SOURCE.StockName, SOURCE.Rate)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action,
       DELETED.StockId AS TargetStockId,
       DELETED.StockName AS TargetStockName,
       DELETED.Rate AS TargetRate,
       INSERTED.StockId AS SourceStockId,
       INSERTED.StockName AS SourceStockName,
       INSERTED.Rate AS SourceRate;

SELECT @@ROWCOUNT;
GO

When the run the above script the output is 2 records updates, 1 record delete, and 2 record inserts.

If we select all records from the Stock table, we can see the final results:

We can see the Tea and Coffee rate was updated, Chips was deleted and Cold Drinks and Chocolate was inserted.

 

I hope this article helps you and you will like it.