Btukfyl

I'm trying to understand why in a scenario like the following

DECLARE @source TABLE 

             (

                 orderId NVARCHAR(50),

                 customerId NVARCHAR(50)

             )



DECLARE @target TABLE 

             (

                  orderId NVARCHAR(50) PRIMARY KEY,

                  customerId NVARCHAR(50) NOT NULL

             )



INSERT INTO @source 

VALUES ('test', '123'), ('test', '234')



MERGE @target AS TRG

USING (SELECT DISTINCT orderId, customerId

       FROM @source) AS SRC ON SRC.orderId = TRG.orderId



WHEN MATCHED THEN

    UPDATE SET TRG.customerId = SRC.customerId



WHEN NOT MATCHED BY TARGET THEN

    INSERT (orderId, customerId)

    VALUES (orderId, customerId);

I'm getting a duplicate key violation error:

Msg 2627, Level 14, State 1, Line 21

Violation of PRIMARY KEY constraint 'PK__#B3D7759__0809335D4BE1521F'. Cannot insert duplicate key in object 'dbo.@target'. The duplicate key value is (test).

What I expect is that the update statement finds the existing key and updates the customerId so that at the end I have in @target 1 row with orderId = 'test' and customerId = '234'.

For what I can assume, it instead tries to insert all records as it first doesn't find any key match at the beginning of the merge, causing the violation as the source contains the key multiple times.

Is this right? Is there any way to achieve what I am expecting using the merge function?

@user1443098

I've read your link, thanks. However I have a massive data insertion coming from a source table and going into 10 different tables; I tried to implement the procedure with a cursor and it took like 0.5s per record (with all the if exists statements). With merge statement, 300 rows have been inserted in the 10 different tables in less than one sec. So in my case it does a lot of difference in performance terms.

There are two records in @source with the same OrderID. There is not a match for either record in @target so the NOT MATCHED clause is trying to insert both of these records. But it can not do this because the primary key on OrderID in the @target table requires that all inserted records have unique values for OrderID. The duplication of values into the primary key causes the primary key violation.

If you are expecting duplicates are possible in the source... you should eliminate them in your USING sub-query. Something like this:

(SELECT orderId, max(customerId) customerId

FROM @source

group by orderId)