compare tables

[bigodines]

Is there any app that compare two tables that should be identical showing which data dont mach?

[lilleman]

Hi,

I am not sure that I understand what you mean. Do you want to obtain the data that does not exist in both tables?

Yours, Erik.

[bigodines]

actually i´ve two tables in different databases (one here at my local machine and the other on my server). I must compare the data between then to see what has been changed. The only problem is that the primary keys aren´t the same (eg.. here at my local the 'id' field starts with 1 and at my server it starts with '550')

[r937]

surely there are some other columns in the table that determine uniqueness

whenever people use an auto_increment as a primary key, this type of situation can happen

however, an auto_increment primary key is a surrogate key, and there should/must be some other column (or set of columns) that would uniquely identify a row

these are the columns that you would use in the FULL OUTER JOIN

also, is there a possibility that some non-key attributes might be different? you would also then want to test for those

for example, suppose the table consists of 3 columns

id -- auto_increment primary key (surrogate)
name -- unique (the "real" or candidate key)
birthday

now let's say you have

database 1 -- id=3, name=fred, birthday=1956-03-05
database 1 -- id=553, name=fred, birthday=1956-05-03

you can match on name, but will you also want to detect data differences?

[bigodines]

yes. I need to compare the DATA 'ignoring' the primary keys (because if it doesnt ignore it, all data will be different to the app). For example:
i´ve a table that contains my products and prices. I would like to have a way to compare the diferences between my two databases (not tables, databases).

Is there any application that do that?

< sorry about my bad english >

[r937]

your english is fine

you cannot compare databases, only tables

no, i have never heard of such an application

(that does not mean one does not exist)

why not just write your own queries to do the compare?

[bigodines]

that´s what im doing but its taking a lot of my time. Anyway, thanks for your help. And congratulations for the award ;P