TSQL merge: doesn't skip duplicate

Question

I used the following code to update or Insert record from one table to another but the insert statement doesn't skip when duplicate record. How can i bypass that?

I get the following error. I want to skip duplicate record and insert the new

Msg 2627, Level 14, State 1, Line 6 Violation of PRIMARY KEY constraint 'PK_MIBOMD_KEY_0'. Cannot insert duplicate key in object 'dbo.MIBOMD'. The duplicate key value is ( , 0, 1). The statement has been terminated.

MERGE [MIBOMD] AS TARGET
USING [whl] AS SOURCE 


ON (TARGET.[bomItem] = SOURCE.ItemID AND TARGET.bomEntry = SOURCE.bomEntry AND TARGET.bomRev = SOURCE.rev)

WHEN MATCHED AND TARGET.bomEntry = SOURCE.bomEntry 
AND TARGET.partId <> SOURCE.partid THEN 
UPDATE SET TARGET.partId = SOURCE.partid

WHEN NOT MATCHED BY TARGET THEN 
INSERT (partId,bomEntry, srcLoc, qty, lead) 
VALUES (SOURCE.partid, SOURCE.bomEntry, 'R14SDS', SOURCE.qty, SOURCE.lead);

Are you sure that your source (`[whl]`) does not have duplicates? — tarheel, Jun 26 '16 at 01:54
@tarheel no, because the primary keys on `[whl]` are `ItemID` and `PartID`. there are multiple or duplicate `Item ID`s only but `ItemID + Part ID` are unique — user6305775, Jun 26 '16 at 02:06
@tarheel no, because the primary keys on [whl] are ItemID and PartID. there are multiple or duplicate Item IDs only but ItemID + Part ID is unique — user6305775, Jun 26 '16 at 02:14
Could you add some Sample data to your question? it is not clear from your code or your description that where the problem is.... — M.Ali, Jun 26 '16 at 02:23
Which columns form the primary key of your source table `dbo.MIBOMD`? — sstan, Jun 26 '16 at 02:59
So there are multiple ItemID and you only matching on ItemID and you wonder why you are getting duplicates? — paparazzo, Jun 26 '16 at 03:07
@user6305775 You are only matching ON (TARGET.[bomItem] = SOURCE.ItemID) and there is no bomRev — paparazzo, Jun 26 '16 at 03:13
I tired too and clean up the question with table def IN the question. And for the second time you are NOT inserting a bomRev. That blank value ( , 0, 3) is not a clue? — paparazzo, Jun 26 '16 at 03:37
`UPDATE SET TARGET.partId = SOURCE.partid` - that's what produces duplicates. — Ivan Starostin, Jun 26 '16 at 11:13

score 1 · Answer 1 · edited Jun 26 '16 at 12:55

As you stated, the uniqueness in the source is guaranteed by ItemID + PartID. Meanwhile, you are matching by source (ItemID,bomEntry,rev)=target.(bomItem,bomEntry,bomRev).

It is entirely possible that you will have something with the same set of (ItemID,bomEntry,rev) in the source but have two different PartID.

Also, among the INSERTed columns you specified, there is no bomRev,bomItem.
Are there default constraints on those columns? Will they guarantee uniqueness?

TSQL merge: doesn't skip duplicate

1 Answers1