SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: compare tables

  1. #1
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    RS, Brazil
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    compare tables

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

  2. #2
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    RS, Brazil
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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')

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    RS, Brazil
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 >

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    RS, Brazil
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that┤s what im doing but its taking a lot of my time. Anyway, thanks for your help. And congratulations for the award ;P


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •