In a typical Microsoft SQL Server data warehouse, it is common to perform INSERT, UPDATE, and DELETE operations on a target table by matching records from a source table. This process can become cumbersome and time-consuming when using separate DML commands. However, starting with SQL Server 2008, developers can use the MERGE command to streamline these operations into a single statement.

The MERGE command, similar to the UPSERT command in Oracle, allows you to insert rows that don’t exist and update rows that do exist in a target table. It combines the sequence of conditional INSERT, UPDATE, and DELETE commands into a single atomic statement, improving performance and simplifying the code.

Here is the syntax for the MERGE statement:

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> ];

The MERGE statement works as separate INSERT, UPDATE, and DELETE statements within the same statement. You specify a “Source” record set and a “Target” table, and define the JOIN condition between the two. Then, you specify the type of data modification to occur when the records between the two datasets are matched or not matched.

Here is an example of using the MERGE command to synchronize a target table with an updated list of products:

-- Create a target table
CREATE TABLE Products
(
   ProductID INT PRIMARY KEY,
   ProductName VARCHAR(100),
   Rate MONEY
) 

-- Insert records into target table
INSERT INTO Products
VALUES
   (1, 'Tea', 10.00),
   (2, 'Coffee', 20.00),
   (3, 'Muffin', 30.00),
   (4, 'Biscuit', 40.00)

-- Create a source table
CREATE TABLE UpdatedProducts
(
   ProductID INT PRIMARY KEY,
   ProductName VARCHAR(100),
   Rate MONEY
) 

-- Insert records into source table
INSERT INTO UpdatedProducts
VALUES
   (1, 'Tea', 10.00),
   (2, 'Coffee', 25.00),
   (3, 'Muffin', 35.00),
   (5, 'Pizza', 60.00)

-- Synchronize the target table with refreshed data from source table
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE 
ON (TARGET.ProductID = SOURCE.ProductID) 
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate 
THEN UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate 
WHEN NOT MATCHED BY TARGET 
THEN INSERT (ProductID, ProductName, Rate) VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
WHEN NOT MATCHED BY SOURCE 
THEN DELETE 

SELECT * FROM Products

After running the above code, the target table (Products) will be synchronized with the refreshed data from the source table (UpdatedProducts). The MERGE statement will update existing records, insert new records, and delete records that no longer exist in the source table.

The MERGE statement offers several key benefits:

• Improved performance: The MERGE statement reads and processes the data only once, compared to writing separate INSERT, UPDATE, and DELETE statements that evaluate and process the data multiple times.
• Simplified code: The MERGE statement combines multiple data operations into a single statement, reducing the complexity and improving maintainability of the code.
• Atomicity: The MERGE statement ensures that all data modifications are performed as a single atomic operation, preventing inconsistencies in the data.

By utilizing the SQL Server MERGE command, developers can streamline their data operations and improve the efficiency of their SQL scripts and stored procedures.