SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Create Customers table
CREATE TABLE Customers
(
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Email NVARCHAR(100)
);

-- Insert sample data
INSERT INTO Customers
(
    CustomerID,
    Name,
    Email
)
VALUES
(1, 'John Smith', 'john@example.com'),
(2, 'Jane Doe', 'jane@example.com'),
(3, 'Mark Brown', 'mark@example.com');

-- Create CustomerUpdates table
CREATE TABLE CustomerUpdates
(
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Email NVARCHAR(100)
);

-- Insert sample data
INSERT INTO CustomerUpdates
(
    CustomerID,
    Name,
    Email
)
VALUES
(1, 'John Johnson', 'john.j@example.com'),
(3, 'Mark Brown', 'updated_mark@example.com'),
(4, 'Emily White', 'emily@example.com');
6 rows affected

-- Target table for MERGE statement
SELECT *
FROM Customers;


CustomerID
Name
Email


1
John Smith
john@example.com

2
Jane Doe
jane@example.com

3
Mark Brown
mark@example.com


-- Source table for MERGE statement
SELECT *
FROM CustomerUpdates;


CustomerID
Name
Email


1
John Johnson
john.j@example.com

3
Mark Brown
updated_mark@example.com

4
Emily White
emily@example.com


-- Create CustomerUpdatesLog table
CREATE TABLE CustomerUpdatesLog
(
    CustomerUpdatesLogID INT IDENTITY(1, 1) PRIMARY KEY,
    Action NVARCHAR(10),
    CustomerID INT,
    Name NVARCHAR(100),
    Email NVARCHAR(100),
    NewName NVARCHAR(100),
    NewEmail NVARCHAR(100),
    ChangeTimestamp DATETIME
);

-- MERGE CustomerUpdates table into Customers table
MERGE Customers AS TARGET
USING CustomerUpdates AS SOURCE
ON TARGET.CustomerID = SOURCE.CustomerID
WHEN MATCHED THEN
    UPDATE SET TARGET.Name = SOURCE.Name,
               TARGET.Email = SOURCE.Email
WHEN NOT MATCHED THEN
    INSERT
    (
        CustomerID,
        Name,
        Email
    )
    VALUES
    (SOURCE.CustomerID, SOURCE.Name, SOURCE.Email)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action,
       Deleted.CustomerID,
       Deleted.Name,
       Deleted.Email,
       Inserted.Name,
       Inserted.Email,
       GETDATE()
INTO CustomerUpdatesLog;

-- Display records in Customers table after MERGE
SELECT *
FROM Customers;

-- Display logs outputted by MERGE statement
SELECT *
FROM CustomerUpdatesLog;


CustomerID
Name
Email


1
John Johnson
john.j@example.com

3
Mark Brown
updated_mark@example.com

4
Emily White
emily@example.com




CustomerUpdatesLogID
Action
CustomerID
Name
Email
NewName
NewEmail
ChangeTimestamp


1
UPDATE
1
John Smith
john@example.com
John Johnson
john.j@example.com
2023-09-06 20:09:47.017

2
DELETE
2
Jane Doe
jane@example.com
null
null
2023-09-06 20:09:47.017

3
UPDATE
3
Mark Brown
mark@example.com
Mark Brown
updated_mark@example.com
2023-09-06 20:09:47.017

4
INSERT
null
null
null
Emily White
emily@example.com
2023-09-06 20:09:47.017

CustomerID	Name	Email
1	John Smith	john@example.com
2	Jane Doe	jane@example.com
3	Mark Brown	mark@example.com

CustomerID	Name	Email
1	John Johnson	john.j@example.com
3	Mark Brown	updated_mark@example.com
4	Emily White	emily@example.com

CustomerUpdatesLogID	Action	CustomerID	Name	Email	NewName	NewEmail	ChangeTimestamp
1	UPDATE	1	John Smith	john@example.com	John Johnson	john.j@example.com	2023-09-06 20:09:47.017
2	DELETE	2	Jane Doe	jane@example.com	null	null	2023-09-06 20:09:47.017
3	UPDATE	3	Mark Brown	mark@example.com	Mark Brown	updated_mark@example.com	2023-09-06 20:09:47.017
4	INSERT	null	null	null	Emily White	emily@example.com	2023-09-06 20:09:47.017