[SOLVED] MERGE INTO triggering duplicate key value specified

MERGE INTO triggering duplicate key value specified

Table A has a simple structure:

ID
FOREIGN_ID
ORIGIN

There is a unique key over FOREIGN_ID and ORIGIN to prevent duplicates.

Table B has many fields, but of interest is

ORIGIN_ID
ORIGIN

Table B can have many rows with the same origin and origin_id. My goal is to create an insert trigger on table B that will insert only unique ORIGIN_ID/ORIGIN combinations into table A.

But first, I need to make a working insert that will avoid inserting duplicates. When in the trigger, values from the row of Table B will be used rather than these hard-coded values

MERGE INTO MYLIB.TABLE_A AS TARGET
USING
        ( SELECT FOREIGN_ID , ORIGIN FROM MYLIB.TABLE_A ) AS SRC
        ON SRC . FOREIGN_ID = '123' AND SRC . ORIGIN = 'ABC'
WHEN NOT MATCHED THEN
        INSERT ( FOREIGN_ID , ORIGIN ) VALUES ( '123' , 'ABC' )

The first time I execute the statement, it works as expected. A row is added to table A.
The second time it is executed, I expect it to do nothing, with no errors.

But I get

[Code: -803, SQL State: 23505]  [SQL0803] Duplicate key value specified.

What am I missing or not doing correctly?

Solution

That's not the way to hardcode a test merge...

You want to use a "Table Value Constructor" (TVC).

Try this (expanded to 2 rows so you can see how to use a TCV):

MERGE INTO MYLIB.TABLE_A AS TARGET
USING
  ( values ('123','ABC'),('456','DEF')) as SRC (FOREIGN_ID, ORIGIN)
on SRC.FOREIGN_ID = TARGET.FOREIGN_ID AND SRC.ORIGIN = TARGET.ORIGIN
WHEN NOT MATCHED THEN
        INSERT ( FOREIGN_ID , ORIGIN ) VALUES ( SRC.FOREIGN_ID, SRC.ORIGIN );