Write a SQL MERGE statement that updates records when they match, inserts new records when they don't, and deletes records that are not present in the source.

Assuming we have an employees_source table (new data) and an employees_target table (current data), the MERGE can be written as:

MERGE INTO employees_target AS tgt
USING employees_source AS src
ON tgt.employee_id = src.employee_id
WHEN MATCHED THEN
    UPDATE SET
        tgt.first_name = src.first_name,
        tgt.last_name  = src.last_name,
        tgt.department = src.department,
        tgt.salary     = src.salary
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, department, salary)
    VALUES (src.employee_id, src.first_name, src.last_name, src.department, src.salary)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Explanation:

• The WHEN MATCHED clause updates existing rows.
• The WHEN NOT MATCHED clause inserts rows that are new.
• The WHEN NOT MATCHED BY SOURCE clause deletes rows that no longer exist in the source.

Complexity: The operation is O(n) relative to the number of rows in the source, with the database engine handling hashing or indexing internally. The statement is atomic, ensuring consistency without separate transaction control.

This pattern is ideal for nightly batch jobs that keep a reporting or analytics table in sync with operational data.