[SOLVED] Why is the MERGE statement throwing a unique key constraint error

Why is the MERGE statement throwing a unique key constraint error

I'm attempting to run a merge statement between two tables, Table A and Table B. The statement is supposed to update records is a match exists on the designated field (Name) and to insert a record is no match exists.

When the merge statement executes it's throwing the following error:

Violation of UNIQUE KEY constraint 'AK_UniqueName'. Cannot insert duplicate key in object 'dbo.Table B'. The duplicate key value is (A. Adams).

Merge Statement, Table A, Table B and desired result follow:

Merge Statement

BEGIN
MERGE dbo.TableB AS T
USING dbo.TableA AS S
ON T.Name LIKE S.Name
WHEN MATCHED THEN
UPDATE SET T.Lname = IsNULL(S.Lname,T.Lname),          
WHEN NOT MATCHED THEN 
INSERT (Name, Lname);
END

Table A

**Name**   **Lname**
A. Adams   Adams
B. Adams   Adams

Table B

**Name**   **Lname**
A. Adams   Adams
C. Adams   Adams

Desired Result (Table B after merge - with one new row)

**Name**   **Lname**
A. Adams   Adams
B. Adams   Adams
C. Adams   Adams

Solution

Typically this occurs either when there are duplicate rows in your source, or if you are updating the primary key.

This is why I rarely use MERGE. Instead I use separate UPDATE and INSERT statements which allows you to isolate the issue to either the insert or update step.

ON T.Name LIKE S.Name is unlikely to be correct. Please try ON T.Name = S.Name instead