Merge Statament

 Merge statement  in SQL Server is used to perform insert, update, or delete operations on a target table based on the results of a join with a source table. It's commonly used for synchronizing two tables. Here's a simple example:

Let's say we have two tables: TargetTable and SourceTable, both with columns ID and Value.

sql

-- Create sample tables
CREATE TABLE TargetTable (
    ID INT PRIMARY KEY,
    Value VARCHAR(50)
);

CREATE TABLE SourceTable (
    ID INT PRIMARY KEY,
    Value VARCHAR(50)
);

-- Insert sample data into TargetTable
INSERT INTO TargetTable (ID, Value)
VALUES
    (1, 'Value 1'),
    (2, 'Value 2'),
    (3, 'Value 3');

-- Insert sample data into SourceTable
INSERT INTO SourceTable (ID, Value)
VALUES
    (2, 'Updated Value 2'),
    (3, 'Value 3'),
    (4, 'Value 4');

Now, let's use MERGE to synchronize TargetTable with SourceTable:

sql

-- Merge operation
MERGE INTO TargetTable AS target
USING SourceTable AS source
ON (target.ID = source.ID)

-- When records are matched, update the values
WHEN MATCHED THEN
    UPDATE SET target.Value = source.Value

-- When there is no match, insert the records
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Value) VALUES (source.ID, source.Value)

-- When there is no match in the source, delete the records in the target
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- Display the updated TargetTable
SELECT * FROM TargetTable;

After executing the MERGE statement, the TargetTable will be synchronized with the SourceTable. In this example, the row with ID 2 will be updated with the new value from SourceTable, the row with ID 4 will be inserted into TargetTable, and the row with ID 1 will be deleted from TargetTable because it doesn't exist in SourceTable.