Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    30

    Question Unanswered: How to compare 2 tables ot different DBs in Oracle style

    In Oracle this is done this way :

    SQL>
    1 select ID, NAME from TABLENAME
    2* minus select ID,NAME from TABLENAME@DATABASE2
    SQL>

    Any ideas ?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah....minus is a way to do a non existance checj=k...or a left outer join where the right table key is null...

    I don't know what your talking about with @DATABASE2
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by Brett Kaiser
    I don't know what your talking about with @DATABASE2
    That's Oracle speak for DATABASE2..TABLENAME in SQL-92.

    -PatP

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    See...my Oracle naivite' is showing....

    Never dealt with more than 1 db in Oracle....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2004
    Posts
    30

    Question

    select ID, NAME from TABLENAME
    minus select ID,NAME from TABLENAME@DATABASE2

    This statement compares table TABLENAME in current database and DATABASE2 and shows the lines that are missing in the current database.

    If it returns

    1 | Test1
    2 | Test2

    this meanse that these 2 lines exists in TABLENAME in current DB, and doest not exists in TABLENAME in DATABASE2.

    In SQL Server it should be something like this :

    SELECT ID, NAME FROM TABLENAME
    MINUS
    SELECT ID,NAME FROM DATABASE2..TABLENAME

    but we don't have MINUS in T-SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Might I suggest using a FULL OUTER JOIN to see what was added or deleted, then a compare of the column values to see what keys have different values associated with them?

    -PatP

  7. #7
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578

    Re: How to compare 2 tables ot different DBs in Oracle style

    FYI, http://www.sql-server-performance.co...parison_sp.asp

    Originally posted by The-Saint
    In Oracle this is done this way :

    SQL>
    1 select ID, NAME from TABLENAME
    2* minus select ID,NAME from TABLENAME@DATABASE2
    SQL>

    Any ideas ?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

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