Comparing data in joined tables

DBADave

I'm evaluating SQL Data Compare and was wondering whether it can do the following.

I have a typical parent-child table relationship, the parent table holds financial instrument data, keyed on an identity column, but with a unique constraint on a system generated instrument identifier. I have a child table containing the various external identifiers that an instrument can have, keyed on the external identifier & the external identifier type. There is also a foreign key to the parent table on the identity column. Sadly the identity values between the two versions of the table do not match.

Now, the tool will allow me to compare the two tables in isolation, however what I would like to be able to do is compare a resultset that covers both tables, i.e. the instruments core data and its various external identifiers.

The only way I can see to do this is to create a view that gives me this resultset and let the tool do the comparison on that.

Is there any other way of doing this kind of comparison?

Brian Donahue

Hello,

Data Compare wants to compare and sync rows of data using the primary key or other unique index. If the primary key column is an identity with different seeds, you would either need to construct some sort of indexed views on the table or use SQL Data Compare's mapping functions to specify a different column or set of columns as the comparison key other than the primary key and tell Data Compare not to synchrionize the primary key column.

DBADave

Thanks, I thought that might be the case.

Kai Iske

Hi,

I just started evaluating v5 of SQL Bundle to find out whether it really makes sense for us to upgrade from v3. Actually I was hoping to get our problem solved with the new feature of custom comparison keys.

However I mocked up a simple sample to check master / detail relationships and synchronization. This is what it looks like (*) denotes Primary Key / Identity Column:

TBL_Item
Item_SID (*)
ItemName

TBL_ItemDetail
ItemDetail_SID (*)
Item_SID
ItemDetailName

So there is a foreign key constraint from TBL_ItemDetail.Item_SID to TBL_Item.Item_SID.

Now to simulate identities that are not in sync, I created the following entries within the tables:

"Left" database
TBL_Item
1 Item 1
2 Item 2

TBL_ItemDetail
1 1 Item Detail 1
2 2 Item Detail 2

"Right" database
TBL_Item
1 Item 3
2 Item 4

TBL_ItemDetail
1 1 Item Detail 3
2 2 Item Detail 4

Ok, off we go: I wanted to sync the "left" entries into the "right" database that should give me something like this:

TBL_Item
1 Item 3
2 Item 4
3 Item 1
4 Item 2

TBL_ItemDetail
1 1 Item Detail 3
2 2 Item Detail 4
3 3 Item Detail 1
4 4 Item Detail 2

However, no matter what I set the custom keys to, whether I include / exclude identity columns, whether I change mapping, SQL Data Compare does either of the following:

- Drop the existing entries (Item 3 / Item 4) on the target database
- Create the new entries but with the details pointing to the wrong foreign keys

I though that with the information of foreign keys being set between the tables, SQL Data Compare would create something like:

INSERT INTO [Master]
SELECT @id = SCOPE_IDENTITY()
INSERT INTO [Detail]

Or something like

INSERT INTO [Master]
...

INSERT INTO [Detail] SELECT Master_SID, ... FROM [Master] WHERE CustomKey = x
INSERT INTO [Detail] SELECT Master_SID, ... FROM [Master] WHERE CustomKey= y

But maybe I'm totally wrong here and simply misinterpreted something

Any help / hint would be highly appreciated

Thank you very much in advance

Kai Iske

Brian Donahue

Hello Kai,

Data Compare doesn't consider foreign key relationships at all; just the data in the table. The only option you can control is whether or not you want the foreign keys disabled for the duration of the synchronization.

Kai Iske

Hi Brian,

thanks for the feedback. However as I believe this is a rather common scenario, what would be the best approach to do this? If it's not possible at all using SDC, is there anything planned in the future?

Regards

Kai

Kai Iske

Hi there,

any news?

Kai

Brian Donahue

If the identity (comparison keys) columns do not contain the same values between databases, then it's going to be a problem. Possibly using a different column or columns as the row identifier and using a custom Data Compare mapping may be a workaround.