MERGE statement in SQL Server to perform upserts (update/inserts).

The MERGE statement in SQL Server is a powerful way to perform "upserts" (a combination of inserts and updates). It allows you to merge data from a source table into a target table based on a specified condition, handling insert, update, and delete operations in one statement.

Here's the general syntax for a MERGE statement:

MERGE INTO target_table AS target
USING source_table AS source
ON target.join_column = source.join_column
WHEN MATCHED THEN
    UPDATE SET 
        target.column1 = source.column1,
        target.column2 = source.column2,
        ...
WHEN NOT MATCHED BY TARGET THEN
    INSERT (column1, column2, ...)
    VALUES (source.column1, source.column2, ...)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE; -- Optional

Example Scenario

Suppose we have two tables: Sales (Target) and SalesUpdates (source). We want to update existing records in Sales with data from SalesUpdates where there's a match on SaleID. If no match is found, we want to insert the new records from SalesUpdates into Sales.

Example Code

Creating Sample Tables and Inserting Data

-- Drop tables if they exist
DROP TABLE IF EXISTS Sales;
DROP TABLE IF EXISTS SalesUpdates;

-- Create target table Sales
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ProductID INT,
    Quantity INT,
    SaleDate DATE
);

-- Insert sample data into Sales
INSERT INTO Sales (SaleID, ProductID, Quantity, SaleDate) VALUES
(1, 101, 10, '2023-01-01'),
(2, 102, 15, '2023-01-02'),
(3, 103, 20, '2023-01-03');

-- Create source table SalesUpdates
CREATE TABLE SalesUpdates (
    SaleID INT,
    ProductID INT,
    Quantity INT,
    SaleDate DATE
);

-- Insert sample data into SalesUpdates
INSERT INTO SalesUpdates (SaleID, ProductID, Quantity, SaleDate) VALUES
(2, 102, 25, '2023-01-02'), -- Existing SaleID with updated Quantity
(3, 104, 20, '2023-01-03'), -- Existing SaleID with updated ProductID
(4, 105, 30, '2023-01-04'); -- New SaleID to be inserted

Sales (Target Table):

SaleID	ProductID	Quantity	SaleDate
1	101	10	2023-01-01
2	102	15	2023-01-02
3	103	20	2023-01-03

SalesUpdates (Source Table):

SaleID	ProductID	Quantity	SaleDate
2	102	25	2023-01-02
3	104	20	2023-01-03
4	105	30	2023-01-04

MERGE Statement

-- Perform the MERGE operation
MERGE INTO Sales AS target
USING SalesUpdates AS source
ON target.SaleID = source.SaleID
WHEN MATCHED THEN
    UPDATE SET 
        target.ProductID = source.ProductID,
        target.Quantity = source.Quantity,
        target.SaleDate = source.SaleDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (SaleID, ProductID, Quantity, SaleDate)
    VALUES (source.SaleID, source.ProductID, source.Quantity, source.SaleDate)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE; -- Optional: Uncomment if you want to delete records not found in the source

Resulting Sales Table After MERGE Operation

After executing the MERGE the statement, the resulting Sales table will look like this:

SaleID	ProductID	Quantity	SaleDate
1	101	10	2023-01-01
2	102	25	2023-01-02
3	104	20	2023-01-03
4	105	30	2023-01-04

Explanation

Drop Tables: This ensures that any previous versions of the tables are removed before creating new ones.
Create Tables: Sales and SalesUpdates tables are created with the same structure.
Insert Sample Data: Example data is inserted into both tables to illustrate the merge operation.
MERGE Statement:
- WHEN MATCHED: Updates existing records in the Sales table with data from SalesUpdates.
- WHEN NOT MATCHED BY TARGET: Inserts new records from SalesUpdates into Sales.
- WHEN NOT MATCHED BY SOURCE: Optionally deletes records from Sales that are not found in SalesUpdates.

Final State of Sales Table

After running the MERGE statement, the Sales the table will be updated as follows:

SaleID 2 will have Quantity updated to 25.
SaleID 3 will have ProductID updated to 104.
SaleID 4 will be newly inserted with ProductID 105, Quantity 30, and SaleDate 2023-01-04.

If the DELETE the clause is included, any SaleIDs in Sales that are not in SalesUpdates will be removed.

Optional DELETE Clause

You can also include a DELETE clause to remove records from the target table that do not exist in the source table:

WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

This complete MERGE statement ensures that:

Records in the target table that match the source table are updated.
Records in the source table that do not match the target table are inserted.
Records in the target table that do not exist in the source table are deleted (if the DELETE the clause is included).

Explain the SQL triggers and their uses

Explain the SQL Server backups and their types

How to use searching and filtering data in an SQL server?

How can I create and use a calculated column in a SQL Server query?