Using multiple match criteria in SQL server merge

Question

At this point of time Source Table and destination Table is having Three columns 'ID', 'ST' and 'ADD'. 'ADD' allows null

Using SQL Server merge statement in I want to put changes made in source Table to destination. Requirement:

1. If ID is available in source but not in target then insert a new row in target
2. For any ID available in both source and target, if only ADD is changes in source then insert new row in target. i.e If ADD for ID=2 changed twice, then we will be having three rows(original + two changes) for ID=2 in target, as we are inserting new row every time
3. For any ID available in both source and target, if only ST changes in source then modified value of ST should be updated in all rows for matched ID in target
4. For any ID available in both source and target, if both ST and ADD changes in source, then new row should be inserted for new ADD and all old rows should updated for that ID with updated value of ST

Can I achieve this with SQL Server merge? If not, what are the alternatives we have?

Answer 1

Try merge with st as unique column and update id, add columns with multiple records

MERGE Tablex AS TARGET
USING Vw_tablex AS SOURCE
    ON (TARGET.id = SOURCE.id)
WHEN MATCHED
    AND
    --updates only St column as unique
    target.st <> source.st
    THEN
        UPDATE
        SET
            --- Inserts/updates multiple values in add column 
            TARGET.id = SOURCE.id
            ,TARGET.ADD = Source.ADD
            ,TARGET.st = SOURCE.st
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT (
            id
            ,ADD
            ,st
            )
        VALUES (
            SOURCE.id
            ,source.ADD
            ,source.st
            );

Answer 2

MERGE needs primary key, you have to join two tables in such a way that rows are matched one-to-one. Otherwise you'll get an error similar to this:

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

Your second requirement essentially means that the key consists of two columns (ID, ADD) and the MERGE statement should join on both columns:

MERGE INTO DstTable AS Dst
USING
(
    SELECT
        ID
        ,ST
        ,ADD
    FROM SrcTable
) AS Src 
ON  Dst.ID = Src.ID
AND Dst.ADD = Src.ADD
WHEN MATCHED AND Dst.ST <> Src.ST THEN
UPDATE SET
    Dst.ST = Src.ST
WHEN NOT MATCHED BY TARGET THEN
INSERT
    (ID
    ,ST
    ,ADD)
VALUES
    (ID
    ,ST
    ,ADD)
;

This query does almost what you want. Updating of ST values is not quite as you described.

3. For any ID available in both source and target, if only ST changes in source then modified value of ST should be updated in all rows for matched ID in target

Since the query above joins on (ID, ADD), the modified value of ST would be updated not in all rows for matched ID in target, but in one row with matched ID and ADD.

Similarly, the fourth requirement is not quite as you wanted. The new row will be inserted, but none of the old rows with the same ID would be updated.

For this kind of updates of ST you'll need a separate UPDATE statement. Or separate INSERT and UPDATE statements without MERGE at all.