Merge Statement giving error once last name gets changed in Source Table

Question

I have source table called payroll in a staging database. I am using Merge statement which inserts employee data from Staging to Expense database. Once employees get married and their last name is changed, I get this error:

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

I have a unique index on EmployeeID in the Employee table. For an example one month I get a file which has Unique id of 10072 first name Abby and last name smith once Abby gets married her last name changes to Marshall but same first name and employee id. What should I do?

Here is my merge statement.

MERGE INTO Dimension.Employee AS T
        USING 
            (
                SELECT DISTINCT 
                    LTRIM(RTRIM(EmplID)) AS EmployeeID
                    ,LTRIM(RTRIM(FirstName)) AS FirstName
                    ,LTRIM(RTRIM(LastName)) AS LastName
                FROM Staging.PayRoll
            ) AS S
            ON T.EmployeeID = S.EmployeeID
                WHEN NOT MATCHED THEN 
                    INSERT (EmployeeID, FirstName, LastName)
                    VALUES (S.EmployeeID, S.FirstName, S.LastName)
                WHEN MATCHED THEN UPDATE SET
                    T.FirstName = S.FirstName
                    , T.LastName = S.LastName
                    ;

Answer 1

It sounds like this bit of code is generating 2 rows for the same ID, when a name changes the Payroll table has 2 EmpIDs.

            SELECT DISTINCT 
                LTRIM(RTRIM(EmplID)) AS EmployeeID
                ,LTRIM(RTRIM(FirstName)) AS FirstName
                ,LTRIM(RTRIM(LastName)) AS LastName
            FROM Staging.PayRoll

The merge statement is then trying to update the same row in the Target table twice. In theory you can't do this because SQL can return data in any ORDER so you can't apply your 2 updates in the correct sequence.

Here's an example of the error and possible solution. (sorry, I don't have enough info about Payroll table to provide anything more)

DROP TABLE #Payroll
CREATE TABLE #Payroll (ID INT
                    ,EmplID INT
                    ,Name VARCHAR(10)
                    )

INSERT INTO #Payroll VALUES
(1,10,'Bill')
,(2,10,'Bill')
,(3,20,'John')
,(4,20,'John')
,(5,30,'Stephen')
,(6,30,'Steven') --EmpID 30 changes his name

--Steven changed name, but the query is generating a duplicate the MERGE will not be happy
SELECT DISTINCT 
    LTRIM(RTRIM(EmplID)) AS EmployeeID
    ,LTRIM(RTRIM(Name)) AS FirstName
FROM #Payroll

--Solution
SELECT DISTINCT 
    LTRIM(RTRIM(P.EmplID)) AS EmployeeID
    ,LTRIM(RTRIM(P.Name)) AS FirstName
FROM #Payroll P
    CROSS APPLY (SELECT TOP 1 * FROM #Payroll T
                WHERE T.EmplID = P.EmplID 
                ORDER BY ID DESC) L
WHERE
    L.ID = P.ID