2

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
Mutuelinvestor
  • 3,384
  • 10
  • 44
  • 75
  • 2
    Either there are duplicate rows in your source, or you are updating the primary key. This is why I never use `MERGE`. If this was seperate `UPDATE` and `INSERT` statements you could isolate the issue to one of them.. I suggest you check [Table A] for duplicates. – Nick.Mc Sep 07 '18 at 00:54
  • 1
    Also I don't know the requirements but `ON T.Name LIKE S.Name` is unlikely to be correct. Should you be using `ON T.Name = S.Name` instead? – Nick.Mc Sep 07 '18 at 00:56
  • @Nick.McDermaid - I can confirm that I'm not merging into the same table. Name is not the primary key, but TableB does have a unique constraint on the Name field. But since A. Adams in A matches A. Adams in B I am expecting an Update not an insert. – Mutuelinvestor Sep 07 '18 at 00:59
  • Ah, Let me try running it with = vs Like – Mutuelinvestor Sep 07 '18 at 01:00
  • @Nick.McDermaid - the = sign did the trick. If you care to answer the question the check mark is yours. Many Thanks, – Mutuelinvestor Sep 07 '18 at 01:02
  • Great - I put my comments in an answer below. Based on your sample dataset I'm not sure how this could happen but I'm pleased you solved ti. THE CHECK MARK IS MIIIIINNNNE!!!!! – Nick.Mc Sep 07 '18 at 01:43

1 Answers1

2

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

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91