Trying to do a SQL Merge statement, but it errors -

i've got stored procedure accepts batches of product info. each product, either insert db or update it, if exists.

a product defined composite key -> productcompanyid (where product came from) , productid (the unique id, per company).

i'm trying merge query. works perfectly, until batch has same composite key in more once.

for example, lets imagine have 10 products. 2 of these have same comp-keys different prices.

i thought first 'row' inserted, 2nd row being update.

here is full repo sql code show i've done.

it's far hard try , make sure there's unique composite key's per batch. so, there can do?

i'm using sql server 2012 .. i'm not sure if that's issue.

instead of using @mergedata directly source of merge statement, (should) rewrite use subquery or cte filter duplicate rows , choose correct 1 used in merge

with cte_mergedata  (     select *       , row_number() on (partition productcompanyid, productid order  productid desc) rn --find better order     @mergedata ) merge @sometable t     using (select * cte_mergedata rn = 1) s     on t.productcompanyid = s.productcompanyid , t.productid = s.productid     when matched         update         set t.name = s.name,             t.price = s.price     when not matched         insert (productcompanyid, productid, name, price)         values (s.productcompanyid, s.productid, s.name, s.price)     output s.productcompanyid, s.productid, inserted.id @mergeproductsresults;