Header - Detail comparison

Comments

• Chris Auckland Posts: 769

  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.

  Chris

  0 · Share on Twitter
• DENTSPLY Posts: 4

  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

  0 · Share on Twitter
• dmulvena@bc Posts: 2

  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).

  0 · Share on Twitter
• David Atkinson Redgate › Posts: 1,462 Rose Gold 2

  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

  David Atkinson
  Product Manager
  Redgate Software

  0 · Share on Twitter
• DENTSPLY Posts: 4

  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

  0 · Share on Twitter
• dmulvena@bc Posts: 2

  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.

  0 · Share on Twitter