I have two DB's. To make things easy we will call them Source and Target. I need to compare a table in each DB and if they don't match in Target then I need to either Delete/Update/Insert. The fields I need to compare are the Employee, Percentage and AssignedType. Here is how things break down.
- Matter Originator
Each Client Case is called a 'Matter'. Each 'Matter' can have multiple AssignedTypes and each type can have multiple Employees. At anytime the Employee or Percentage can change for each AssignedType. I need to compare the Source and Target and make sure they are the same. Below is what I have so far. I tried to use the MERGE technique but I am having trouble with a couple of spots. I know that the Target DB can not be the remote DB so I run the code on the Target DB and call the Source DB.
WITH ctePL AS
SELECT m.Matters, mp.AssignedPercent, p.Initials, hbm.Matter_Uno, mp.AssignedType, hbmp.Empl_Uno,
(SELECT TOP 1 ADRT FROM tbl_ADRAssignedType WHERE ProLaw = mp.AssignedType) AS adAssignedType
FROM Source.matters m
JOIN Source.MattersProfessionals mp on m.Matters= mp.Matters
JOIN Source.Professionals p ON mp.Professionals = p.Professionals
JOIN Target.HBM_Matter hbm ON hbm._ProLaw_FK = m.Matters
JOIN Target.HBM_PERSNL hbmp ON hbmp.Initials = p.Initials
MERGE Target.TBM_CLMAT_PART AS Target
USING ctePL AS Source ON Target.Matter_Uno = Source.Matter_Uno AND Target.Part_Cat_Code = Source.AssignedType AND Target.Empl_Uno = Source.Empl_Uno
WHEN NOT MATCHED BY TARGET THEN
INSERT (Row_Uno, Rec_Type, Matter_Uno, Client_Uno, Part_Cat_Code, Eff_Date, To_Date, Empl_Uno, Percentage, Last_Modified)
VALUES ((SELECT LastKey + 1 FROM Target.CMS_UNIQUE_KEYS WHERE TBNAME = 'TBM_CLMAT_PART'), '1', Source.Matter_Uno, '0',
Source.adAssignedType, '2014-02-06 00:00:00.000', '2099-12-31 00:00:00.000', Source.Empl_Uno, 100, '2014-02-06 00:00:00.000')
WHEN MATCHED THEN
UPDATE SET Target.Percentage = Source.AssignedPercent
WHEN NOT MATCHED BY SOURCE AND Target.Matter_Uno = Source.Matter_Uno THEN
The problem with the INSERT section is that the ID or what we call 'Row_Uno' is not an auto increment. Each time we INSERT then we need to get the ID from Target.CMS_UNIQUE_KEYS WHERE TBNAME = 'TBM_CLMAT_PART'. Once we INSERT into the Target table we need to UPDATE Target.CMS_UNIQUE_KEYS.
The problem with the DELETE section is that it DELETES everything. I need help with 'WHEN NOT MATCHED BY SOURCE '.
Thanks in advance.