Suppose you have something like this:
Source table (variable):
Values (
LeftId INT NOT NULL,
RightId INT NOT NULL,
CustomValue varchar(100) NULL
)
Target table:
Mapping (
LeftId INT NOT NULL,
RightId INT NOT NULL,
CustomValue varchar(100) NULL
)
I want to merge Values
into Target
with the following rules:
- Match on
source.LeftId = target.LeftId AND source.RightId = target.RightId
- when matched in target, update
CustomValue
- when not matched in target, insert
- when matched in target, update
- Delete any unmatched values in the target that do match a
LeftId
in the source, i.e. only delete records that related to theLefId
s of what I'm merging.
(That last rule is hard to describe, sorry!)
For instance:
Source:
1, 10, foo
1, 11, foo
Target:
1, 10, bar
1, 12, foo
2, 20, car
Merge result:
Result Target:
1, 10, foo (updated)
1, 11, foo (inserted)
1, 12, foo (deleted)
2, 20, car (unchanged)
So...
Here's what I have so far, which takes care of update
and insert
:
MERGE Mapping AS target
USING (SELECT LeftId, RightId, CustomValue FROM @Values)
AS source (LeftId, RightId, CustomValue)
ON target.LeftId = source.LeftId
AND target.RightId = source.RightId
WHEN NOT MATCHED THEN
INSERT (LeftId, RightId, CustomValue)
VALUES (source.LeftId, source.RightId, source.CustomValue)
WHEN MATCHED THEN
UPDATE SET
CustomValue = source.CustomValue;
How do I do the delete
part of my rule?
WHEN NOT MATCHED BY source AND EXISTS(SELECT * FROM @Values M WHERE M.LeftId = target.LeftId) THEN DELETE;