Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    Munich, Germany
    Posts
    35

    Unanswered: sql for comparing two tables

    Hi everyone,

    I have 2 seperate databases and I need to check for rows that are different from each other in a table.

    I used access to link the tables in a database and am using queries to check the tables. However, I am having trouble formulating the SQL. What I want to do is not just check for the ID field to see if it exists, but to make sure the whole row exists. How can I form an SQL statement for this?

    I tried something like:

    Select * from table1 where Column1 NOT IN (Select Column1 from Table2) AND Column2 NOT IN (Select Column2 from Table2). However, I do not think this is correct. I want to make sure that the rows are compared, not individual values.

    Any help would be appreciated.

    Thanks,
    Pankaj

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    If you are using Access to do you querying why not try out the unmatching query wizard. That will give you a good head start.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by rokslide
    If you are using Access to do you querying why not try out the unmatching query wizard. That will give you a good head start.
    Now I know the basis for your posts

    pankajdaga,

    Look up CHECKSUM and BINARY_CHECKSUM in BOL.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Look, if you have a problem with me just PM me, don't flood threads. I never said it was the best idea, just that it would give him a start. Does CHECKSUM and BINARY_CHECKSUM work in Access which is what he stated he is using?

  5. #5
    Join Date
    Jan 2004
    Location
    Munich, Germany
    Posts
    35
    Hi guys,

    Both suggestions were very helpful. Thank you for replying

    I have a few more questions. I used left joins to match the rows and it seems to work fine, except for a few things. I think I should mention that one of the databases is running on SQL Server and the other on Oracle.

    When I try to compare entries that are null, it always shows that the records are different. It seems that SQL Server and Oracle handle null values seperately. Is there any way to compare these null values properly in Access?

    How can I compare BLOB and Memo fields of the databases. Access would not let me compare entries that have these datatypes.

    Thanks,
    Sincerely,
    Pankaj

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Checksum and Binary_Checksum are a preferred method for doing this, but to my knowledge Access and VB for Applications does not have these functions. Plus I wouldn't be confident that the same values would be returned between identical records in Oracle and SQL Server. I'd be curious to find out, though...

    To compare NULL values, you could replace NULLs in both tables with a common value (a random series that would not appear naturally in the data). Use a formula so you don't actually change the underlying data.

    For comparing TEXT and MEMO fields, the preferred and most cost-effective method is to get a summer intern who will work for peanuts to examine the values character by character.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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