Filtered Merge Statement

Question

This is, probably, a really simple answer, but I am not coming up with it... Any help would be greatly appreciated:

I created a web form, to a stored procedure, that submits userID, deptID, and a string of comma separated itemID's like so. The purpose is to map an employeeID to itemID's for different departments.:

(1, 2, '20,30,90,60')

I use a Table-Valued function to split that in to a proper table like so:

EmployeeID | deptID | ItemID
        1       2       20
        1       2       30
        1       2       90
        1       2       60

I, then, perform a MERGE between a mapping table (ID, EmployeeID, DeptID, ItemID) and the table created from the splitting function before.

MERGE [dbo].[metricEmployeeDimItemDim] AS TARGET
USING (SELECT accountID, deptID, ItemID FROM api.splitItemInts(@employeeId, @deptID, @ItemIDs, ',') ) AS SOURCE
ON TARGET.employeeId = SOURCE.accountID AND TARGET.ItemId = SOURCE.ItemID
WHEN NOT MATCHED BY TARGET THEN
    INSERT (employeeID, deptID, ItemID)
    VALUES (SOURCE.accountID, SOURCE.deptID, SOURCE.ItemID)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

The issue is that I need the merge statement to not clean out the entire TARGET table... I just need it to affect records with matching USER and DEPT ID's (syncing entries for the ItemID)

How can I accomplish this with a MERGE statement?

Thank you, Wes

Filtered Merge Statement

0 Answers0