The problem is that your ON clause must not identify unique rows.

The other issue with your post is that the MERGE statement includes an ean column and the sample data does not, so it is difficult to help. The only thing I can come up with is that ean should be included in the ON clause because the sample data you provided works just fine.

DECLARE @Source TABLE

(

Article INT,

SizeIndex INT,

upc VARCHAR(25),

Market_ID CHAR(2),

LiteralSize DECIMAL(10, 2)

);

DECLARE @Target TABLE

(

Article INT,

SizeIndex INT,

upc VARCHAR(25),

Market_ID CHAR(2),

LiteralSize DECIMAL(10, 2)

);

INSERT INTO @Source

(Article, SizeIndex, upc, Market_ID, LiteralSize)

VALUES

(101, 570, '054871016745', 'cf', 6.5),

(101, 580, '054871016752', 'cf', 7),

(101, 590, '054871016769', 'cf', 7.5),

(101, 600, '054871016776', 'cf', 8),

(101, 610, '054871016783', 'cf', 8.5),

(101, 620, '054871016790, ', 'cf', 9),

(101, 630, '054871016806', 'cf', 9.5),

(101, 640, '054871016813', 'cf', 10),

(101, 650, '054871016820', 'cf', 10.5),

(101, 660, '054871016837', 'cf', 11),

(101, 670, '054871016844', 'cf', 11.5),

(101, 680, '054871016851', 'cf', 12),

(101, 700, '054871016868', 'cf', 13),

(101, 690, '722977171452', 'cf', 12.5),

(101, 720, '722977849504', 'cf', 14),

(101, 740, '722977849511', 'cf', 15),

(101, 710, '885589781607', 'cf', 13.5),

(101, 730, '885589781614', 'cf', 14.5),

(101, 560, '885589799855', 'cf', 6),

(101, 530, '889131549196', 'cf', 4.5),

(101, 550, '889131549202', 'cf', 5.5),

(101, 510, '889131549219', 'cf', 3.5),

(101, 540, '889131549226', 'cf', 5),

(101, 520, '889131549233', 'cf', 4);

INSERT INTO @Target

(Article, SizeIndex, upc, Market_ID, LiteralSize)

VALUES

(101, 510, '889131549219', 'us', 3.5),

(101, 520, '889131549233', 'us', 4),

(101, 530, '889131549196', 'us', 4.5),

(101, 540, '889131549226', 'us', 5),

(101, 550, '889131549202', 'us', 5.5),

(101, 560, '885589799855', 'us', 6),

(101, 570, '054871016745', 'us', 6.5),

(101, 580, '054871016752', 'us', 7),

(101, 590, '054871016769', 'us', 7.5),

(101, 600, '054871016776', 'us', 8),

(101, 610, '054871016783', 'us', 8.5),

(101, 620, '054871016790', 'us', 9),

(101, 630, '054871016806', 'us', 9.5),

(101, 640, '054871016813', 'us', 10),

(101, 650, '054871016820', 'us', 10.5),

(101, 660, '054871016837', 'us', 11),

(101, 670, '054871016844', 'us', 11.5),

(101, 680, '054871016851', 'us', 12),

(101, 690, '722977171452', 'us', 12.5),

(101, 700, '054871016868', 'us', 13),

(101, 710, '885589781607', 'us', 13.5),

(101, 720, '722977849504', 'us', 14),

(101, 730, '885589781614', 'us', 14.5),

(101, 740, '722977849511', 'us', 15);

MERGE @Target AS Target

USING

(

SELECT

*

FROM

@Source AS S

) AS Source

ON (

Target.article = Source.article AND

Target.sizeIndex = LTRIM(RTRIM(Source.sizeIndex)) AND

Target.market_id = LTRIM(RTRIM(Source.market_id))

)

WHEN MATCHED THEN

UPDATE SET

--Target.[ean] = LTRIM(RTRIM(ISNULL(Source.[ean], Target.[ean]))),

Target.[upc] = LTRIM(RTRIM(ISNULL(Source.[upc], Target.[upc]))),

Target.[literalSize] = LTRIM(RTRIM(Source.[literalSize]))

WHEN NOT MATCHED BY TARGET THEN

INSERT

(

[article],

[sizeIndex],

--[ean],

[upc],

[market_id],

[literalSize]

)

VALUES (

LTRIM(RTRIM(Source.[article])),

LTRIM(RTRIM(Source.[sizeIndex])),

--LTRIM(RTRIM(Source.[ean])),

LTRIM(RTRIM(Source.[upc])),

LTRIM(RTRIM(Source.[market_id])),

LTRIM(RTRIM(Source.[literalSize]))

)

OUTPUT

$action

;