SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: compare tables

Hybrid View

  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,350
    Mentioned
    63 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,350
    Mentioned
    63 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
  •