Ignore Primary Key on data compare?

_ARD

Hey guys,

Here is a simple example to explain what we are trying to do.
We have two tables we are trying to compare:

<Table A>
ID NAME TYPE
1 Apple Fruit
2 Banana Fruit
3 Orange Fruit

and

<Table B>
ID NAME TYPE
1 Orange Fruit
2 Apple Fruit
3 Banana Fruit

We are trying to run the data compare, but have the result tell us that the two tables are identical, since the data is the same in each table (if you ignore the primary key which is ID)

Is there anyway to run the data compare tool and tell it to ignore the primary key? In other words we almost want it to make a composite key of NAME and TYPE and then count the number of occurrences in each table and make sure that matches.

Cheers.

Brian Donahue

Thanks for your question. You can use SQL Data Compare to set a custom comparison keyrather than using the default primary key. This could have performance considerations for tables with lots of data, and can cause problems if any duplicate rows exist, but it works well in a pinch if you don't want to map rows on the primary key.