Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2007
    Posts
    197

    Smile Unanswered: database data difference tool

    Hi,
    Is there any tool through we can get the sql query for data difference in two databases

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    For comparing the schema, a quick and dirty technique is to run mysqldump to a text file for each one and then use diff (or a GUI equivalent like BeyondCompare).

    If you use the --order-by-primary-key, it should also work for the data.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or use an appropriate bit of SQL, bearing in mind that MySQL can easily join tables in different databases.

    off hand the access query wizard will create the skeleton SQL you need.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    differences in two tables or differences in two databases?

    for the latter, use a tool

    the marketplace for third-party database tools is wide open, and i am confident that there are tools out there for this specific purpose
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by r937
    differences in two tables or differences in two databases?

    for the latter, use a tool

    the marketplace for third-party database tools is wide open, and i am confident that there are tools out there for this specific purpose
    both. leastaways it worked for me, both databases were runing the same physical server, but in MySQL paralance they were separate schema, separate databases. admittedly not that big, but the two could be stitched together on an ad hoc basis

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how does your access query wizard detect a table that exists in one database but not the other? how does it detect a column in one table but not the other?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry access was to give the exampl of the sql
    the query I'd run would be in MySQL Query browser.

    I was trying to think of a quick and easy way of locating the correct SQL.. Im not suggesting for one minute that that sort of requirement work should be done in Access when there are better candidates elsewhere.

    I'm happy to admit Im dumb, but I'd hope NOT that dumb.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, so using MySQL, how would you write the query to detect when one database has a table that isn't in the other one? using MySQL, how would you write the query to detect when one table has a column that isn't in the other one?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I wouldn't, but the OP did request an ability to detect data differences, which I interpretated as same table, same columns and the OP was looking for discrepancies in data, not the table or column design

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i realize that's what you interpreted (not interpretated )

    where the hell is that original poster, anyway?




    Edit: i just noticed, this is post #11,111 -- w00h00!!!!
    Last edited by r937; 01-25-08 at 21:45.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by r937
    yes, i realize that's what you interpreted (not interpretated )

    where the hell is that original poster, anyway?
    The reason I suggested a dump is because it's a ten minute job to figure out the arguments to mysqldump and diff and you can see what's changed. The OP might be gone because he has his answer.

    using MySQL, how would you write the query to detect when one table has a column that isn't in the other one?
    You just do select colId from foobase.whateversystemtable where colId IS NOT IN (SELECT colId from barbase.whateversystemtable)... I think, I don't do much MySQL, but I'm pretty sure they have catalog tables like most other DBMSs. (Edit: well, I was thinking databases, not tables, so there'd be a where clause in there.)

    I mean, it gets tricky when you have to check the types and downright annoying if there are different versions / products / whatever, but the nice thing about the relational model is that whatever's in the catalog is really all there is.

  12. #12
    Join Date
    Jun 2007
    Posts
    197

    Smile Tool found

    There is tool Navicate for MySQL through which you can find schema as well as data differances

Posting Permissions

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