How to use parent tables to compare child tables that have different unique id's?

JDSX

This is a common problem for us when using Data Compare. Parent table has unique constraint on a char field alongside an identity field which also serves as a one-to-many foreign key in the child table. The child table uses an identity field to distinguish each row but there's no guarantee the id's will be the same across databases so only the parent can be matched but the child table only has the parent_id identity fk which is not a viable comparison key.

sam.blackburn

You'll probably need to create a view (in each database) joining the two tables, enable the "include views" option and select a comparison key using the Tables & Views tab. If you want to deploy data, you might also need to create an insert trigger on the view.

JDSX

sam.blackburn wrote: »

You'll probably need to create a view (in each database) joining the two tables, enable the "include views" option and select a comparison key using the Tables & Views tab. If you want to deploy data, you might also need to create an insert trigger on the view.

Hi,

That'd be ok for a workaround although I can't say I'd be thrilled by the added overhead and "pollution". A better solution would be adding a sort of computed comparison key to Data Compare.

sam.blackburn

Would you mind suggesting this on our UserVoice page? It sounds like a really nice feature, although it would be a bit messy to implement when comparing scripts folders. If enough people are interested, I expect we'd look into implementing it.

JDSX

https://redgate.uservoice.com/forums/147879-sql-data-compare/suggestions/32550040-custom-computed-comparison-key-for-matching-table