October 9, 2014 at 11:03 am
I am trying to reconcile two different databases. Each database holds customer information and some of this information is represented as drop down tables. Here is how it is linked
Structure
Tables Fields
Attribute names Name, AttID, AttValue
Attribute Values AttID, AttValue, CustID, CustCode
Customer DBID, DBCode,CustID, CustCode, FName, LName
Database 1 Sample
Attribute name Attribute Values Customer
Color 8, 0 8, 1, 1234, 6 892, 14, 1234, 6, John, Doe
red 8, 1 8, 2, 1234, 6
blue 8,2
Database 2 Sample
Attribute name Attribute Values Customer
Color 5, 0 5, 3, 1234, 6 892, 14, 7434, 5, John, Doe
red 5, 3 5, 4, 1234, 6
blue 5,4
Within a database, the ATTID and Attcode link the attribute and attribute value tables and they link to the Customer table with CustID and Cust Code. One Customer may have multiple entries within the Attribute value table
The Customer tables in each database are linked by the DBID and DBCode values. The other ID's are not necessarily the same across databases.
The object is to compile a list of Customers where these attribute values vary between databases. There are approximately 56,000 Customers in the customer table and 710,000 entries in the attribute values table.
My attempts to do this have produced cross joins with millions of values returned so I am definitely looking for some guidance here. Thank you
October 9, 2014 at 11:22 am
Perhaps you can change your approach slightly – how about modifying your queries to run independently and produce lists of row counts, sums, checksums, whatever and then comparing those results?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 9, 2014 at 11:28 am
Phil,
Thanks for your reply. I'm not clear on what you are suggesting. The only thing that links the two databases is the custid and custcode values. Based on that I am trying to do a comparison of the attribute values for a given customer in both databases and produce a discrepancy report. I think the fact that there can be multiple values in the attribute value table for a given customers attribute is throwing this off. Thanks
October 9, 2014 at 12:05 pm
Paul Stasny (10/9/2014)
Phil,Thanks for your reply. I'm not clear on what you are suggesting. The only thing that links the two databases is the custid and custcode values. Based on that I am trying to do a comparison of the attribute values for a given customer in both databases and produce a discrepancy report. I think the fact that there can be multiple values in the attribute value table for a given customers attribute is throwing this off. Thanks
I will admit to laziness here – I did not read your post and requirements clearly enough. More thinking required for a good solution, apologies.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 9, 2014 at 12:14 pm
I am wondering whether it would be worth producing properly normalised tables in each of the databases. I'm thinking along the lines of
(CustId, AttId, AttValue)
These three values could form a (possibly NONCLUSTERED) PK in each table.
Your query would then be made easy, I think.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 9, 2014 at 12:15 pm
With the numbers' you're talking about - you're going to have to address a way to compare like attribute to like attribute. Since you said that the attribue ID's might not be consistent in all DB's you probably will need to build a "mapping table" to translate/standardize the attributes from one DB to the other (so you know that color is being compared to color, etc...)
Assuming you had that - you could use "standard" SQL statements to do you compare. Something like:
(Select dbID, CBCode, attid,AttCode, AttValue from <firstDB>
EXCEPT
Select dbID, CBCode, attid,AttCode, AttValue from <SecondDB>)
Union
(Select dbID, CBCode, attid,AttCode, AttValue from <SecondDB>
EXCEPT
Select dbID, CBCode, attid,AttCode, AttValue from <FirstDB>)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 9, 2014 at 12:28 pm
With the numbers' you're talking about - you're going to have to address a way to compare like attribute to like attribute. Since you said that the attribue ID's might not be consistent in all DB's you probably will need to build a "mapping table" to translate/standardize the attributes from one DB to the other (so you know that color is being compared to color, etc...)
Or base your comparison on attribute name rather than Id – assuming they are unique and match perfectly, of course.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 9, 2014 at 1:26 pm
Phil Parkin (10/9/2014)
With the numbers' you're talking about - you're going to have to address a way to compare like attribute to like attribute. Since you said that the attribue ID's might not be consistent in all DB's you probably will need to build a "mapping table" to translate/standardize the attributes from one DB to the other (so you know that color is being compared to color, etc...)
Or base your comparison on attribute name rather than Id – assuming they are unique and match perfectly, of course.
Yes - agreed. As long as you have a rock-solid way to make sure you're comparing apples to apples, that's the critical aspect. Without that you will be drowning in false negatives.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply