Header - Detail comparison

DENTSPLY · September 28, 2009 10:35AM

Is there a way to compare tables based on values in OTHER TABLES?

I'm looking for a way to compare envelope-header-detail tables between production and test databases. The issue is that all of the relational keys are either dynamically generated GUIDS or identity fields, which would all be different between the environments.

The envelope table does include a field I can use as a custom key field, and comparing that table individually between the environments works fine. Now I need a way to compare the data in the header and detail tables based on the relationships with the envelope table.

Example:
Envelope
Envelope_ID (int, identity)
OrderID (GUID)
Unique_Field

Header
Header_ID (int, identity)
Envelope_ID
OrderID
Header_Fields

Detail
Detail_ID (int, identity)
Header_ID
OderID
Detail_Fields

So, basically, I'm looking for a way to determine that the data in the Detail_Fields is the same between environments when the only unique comparison key I have is the value of Unique_Field in the Envelope table.

Let me know if you need any clarification on what I am asking.

Thanks!

Chris Auckland · September 29, 2009 2:37PM

Thanks for your post.

You should be able to do this if you set a WHERE clause on the Detail table.

You can configure this on the 'Tables & Views' tab.

Something like:

where detail_ID in (select unique_Field from Envelope)

I hope this helps.

DENTSPLY · September 30, 2009 9:53AM

Thanks for your reply Chris.

I understand I can use a WHERE clause to limit the data I am comparing, but I still fail to see how I can use that as the comparison key, because the detail_ID field will still hold different values across environments.

The only thing I can think to do is create a view that distributes the unique field from the envelope table to each row of the header and detail tables. However, then there becomes an issue of creating a cartesion join during the comparison... Also, it would be preferable to do this without modifying the production database.

The best solution I can find so far (and I'm not saying it's really a good one) is to compare the envelope table based on the unique field. Then, loop through each record in the envelope table that passed the comparison, obtaining the associated header records and comparing them. Then again, loop through those header records and obtain and compare the associated detail records. But to do that, I really need to use custom code and straight SQL statements. I don't see any way to do that in SQL Data Compare, is there?

Thanks,
John

dmulvena@bc · October 1, 2009 2:42PM

Chris,

Like John, I wish to be able to do a master/detail comparison.

Another example of what John is talking about is comparing table columns in sys.columns. You cannot effectively match column names without also matching table names, yet only the object_id key is shared between the tables. To do the comparison you need to join sys.columns to sys.objects to get back to the table name. (Please realise that I know there are system functions that would get around this, but the example has wide familiarity and shows the kind of problem John is talking about).

David Atkinson · October 5, 2009 9:13AM

There isn't an explicit feature in SQL Data Compare to achieve this, although if you are able to create a view to join the tables, you should be able to achieve what you ask for, as SQL Data Compare would be able to compare and synchronize the views.

However, if it is not possible for you to create a view on these databases, there's not I can suggest at present. I will log this as a feature request and if there is sufficient interest we'll consider adding this to a future release.

Kind regards,

David Atkinson
Product Manager
Red Gate Software

DENTSPLY · October 5, 2009 11:05AM

Thank you for the reply David.

Yes, if I could create a view to join the tables, that would allow me to compare them. However, since the source tables exist in our Production database, I am wary of adding any "unnecesary" objects just for comparison purposes.

However, that does bring up an idea. Is it possible to create a temporary view within SQL Data Compare that can be applied over both the source and destination databases, then the underlying tables compared based on that view? This way, the source tables would not be altered in any way, however the envelope/header/detail relationships could be easily compared.

If that is not an option at this time, I would like to make a request to add that feature. Please let me know if there is a formal process I need to go thorugh to make that request.

Thank you again,

-John Norcott

dmulvena@bc · October 7, 2009 6:23AM

I'd certainly like this feature added too. It seems fundamental to a Data Compare function in a relational database, that this be available.
Thanks for the clear response David.

Header - Detail comparison

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk