Upserting Customer Data with MERGE

Your task is to implement an "upsert" operation for customer data in a database. Upserting means inserting a new record if it doesn't exist, or updating an existing record if it does. This is a common operation when synchronizing data from different sources or when processing periodic updates. You will use the MERGE statement, a powerful SQL construct that handles this logic concisely and efficiently.

Problem Description

You are given two tables: Customers (the target table) and NewCustomerData (the source table containing incoming customer information). You need to update the Customers table based on the data in NewCustomerData.

Requirements:

1. Insert New Customers: If a customer from NewCustomerData does not exist in the Customers table (identified by CustomerID), insert the customer's details into the Customers table.
2. Update Existing Customers: If a customer from NewCustomerData already exists in the Customers table (identified by CustomerID), update their FirstName, LastName, and Email with the values from NewCustomerData.
3. Handle No-Op: If a customer exists in Customers and their details in NewCustomerData are identical, no update should occur. The MERGE statement should handle this implicitly.

Expected Behavior:

After executing the MERGE operation, the Customers table should reflect the combined state of the initial Customers table and the NewCustomerData, with new customers added and existing ones updated as per the rules.

Edge Cases:

• The NewCustomerData table might be empty.
• NewCustomerData might contain duplicates of the same CustomerID. The MERGE statement should typically handle the first match found or have defined behavior based on the specific SQL dialect. For this challenge, assume standard behavior where it processes based on the first match.
• CustomerID is the primary key in Customers and should be unique.

Examples

Example 1:

Initial Customers table:

CustomerID | FirstName | LastName | Email
-----------|-----------|----------|--------------------
1          | Alice     | Smith    | alice.smith@email.com
2          | Bob       | Johnson  | bob.johnson@email.com

NewCustomerData table:

CustomerID | FirstName | LastName | Email
-----------|-----------|----------|--------------------
2          | Robert    | Johnson  | r.johnson@email.com
3          | Charlie   | Brown    | charlie.b@email.com

Output Customers table after MERGE:

CustomerID | FirstName | LastName | Email
-----------|-----------|----------|--------------------
1          | Alice     | Smith    | alice.smith@email.com
2          | Robert    | Johnson  | r.johnson@email.com
3          | Charlie   | Brown    | charlie.b@email.com

Explanation:

• CustomerID 1 remains unchanged as it's not in NewCustomerData.
• CustomerID 2 exists in both tables. FirstName, LastName, and Email are updated from NewCustomerData.
• CustomerID 3 exists in NewCustomerData but not in Customers, so it is inserted.

Example 2:

Initial Customers table:

CustomerID | FirstName | LastName | Email
-----------|-----------|----------|--------------------
10         | David     | Lee      | david.lee@email.com

NewCustomerData table:

CustomerID | FirstName | LastName | Email
-----------|-----------|----------|--------------------
10         | David     | Lee      | david.lee@email.com

Output Customers table after MERGE:

CustomerID | FirstName | LastName | Email
-----------|-----------|----------|--------------------
10         | David     | Lee      | david.lee@email.com

Explanation:

• CustomerID 10 exists in both tables. The data in NewCustomerData is identical to the existing record in Customers. No update or insert occurs.

Example 3: (Empty NewCustomerData)

Initial Customers table:

CustomerID | FirstName | LastName | Email
-----------|-----------|----------|--------------------
5          | Eve       | Adams    | eve.adams@email.com

NewCustomerData table:

(empty)

Output Customers table after MERGE:

CustomerID | FirstName | LastName | Email
-----------|-----------|----------|--------------------
5          | Eve       | Adams    | eve.adams@email.com

Explanation:

• Since NewCustomerData is empty, no operations are performed. The Customers table remains unchanged.

Constraints

• CustomerID is an integer and is the unique identifier for customers.
• FirstName, LastName, and Email are strings.
• The maximum number of rows in Customers is 1,000,000.
• The maximum number of rows in NewCustomerData is 100,000.
• The MERGE operation should complete within 5 seconds.

Notes

The MERGE statement typically has the following structure:

MERGE INTO target_table AS T
USING source_table AS S
ON T.join_column = S.join_column
WHEN MATCHED THEN
    UPDATE SET T.column1 = S.column1, T.column2 = S.column2, ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...)
    VALUES (S.column1, S.column2, ...);

You will need to adapt this structure to fit the Customers and NewCustomerData tables, using CustomerID as the join column. Pay attention to the WHEN MATCHED and WHEN NOT MATCHED clauses.