• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

Comparing data in two tables

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.

AssignedType

  1. Matter Originator
  2. Paralegal
  3. Attorney
  4. Responsible

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
DELETE;

Open in new window


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.
0
huerita37
Asked:
huerita37
  • 2
1 Solution
 
Eugene ZCommented:
may be Merge is not the best statement for your case

check alternative ways
to compare tables data:
for example
NO EXISTS;
EXCEPT and INTERSECT (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms188055.aspx

LEFT RIGHT JOINS

---BTW: what is your SQL SERVER Version , edition Service PACK
is sql 2008 ( you may need to get the latest Service pack that should have some patches for MERGE bugs)
1
 
huerita37Author Commented:
I do have sql 2008.  I will look into your suggestions.

Thanks.
0
 
huerita37Author Commented:
I won't be able to get back to test your solution but I didn't want to leave the question open.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now