Difference between two tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Difference between two tables

  1. #1
    Join Date
    Sep 2002
    Posts
    376

    Difference between two tables

    Hi folks,

    What is the easiest way to find out that the two tables contain the same data ?
    And also how can i find out the difference b/n the two tables.

    The scenario, i happened to copy a table data into other some time back. I wanted to find out whether there is any diff or is the table data same

  2. #2
    Join Date
    Aug 2003
    Posts
    11
    Hi,
    You could write a select with a Minus clause on the 2 tables and find out the difference in data.

    Regards
    Slash
    (MCP)

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    One of the simplest way is get the count from both the tables this will tell u the no. of records on both the tables.

    regards
    anandkl
    anandkl

  4. #4
    Join Date
    Sep 2002
    Posts
    376
    Originally posted by anandkl
    One of the simplest way is get the count from both the tables this will tell u the no. of records on both the tables.

    regards
    anandkl

    What if any of the records are only updated ?
    or one record is deleted and another is addeded ?


    I think Slash's idea sounds good.......
    Last edited by bang_dba; 10-06-2003 at 11:30 AM.

  5. #5
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    as slash said. if the structure is the same just do a

    select * from table1
    minus
    select * from table2

    if not that you'll have to specify the columns you want to check.
    chris
    Last edited by mrchrispy; 10-07-2003 at 05:51 AM.

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    Describe what you mean by "difference." Tables can contain the same data (or subset, thereof), but be different in structure. If you don't know if the data set is still the same, then would you know if the structure has been changed (added/dropped a column)?

  7. #7
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    That slash stuff will/may not give you everything...It could be anyother way round and should be both ways round....

    Code:
    
    Enter user-name: appdev@nick817
    Enter password:
    
    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
    With the Partitioning option
    JServer Release 8.1.7.4.1 - Production
    
    appdev@NICK817.TARRY.LOCAL> create table t1
      2  as
      3  select * from all_objects 
      4  /
    
    Table created.
    
      
    appdev@NICK817.TARRY.LOCAL> create table t2
      2  as
      3   select * from t1
      4  /
    
    Table created.
    
    appdev@NICK817.TARRY.LOCAL> delete from t1
      2  where rownum < 10000
      3  /
    
    9999 rows deleted.
    
    appdev@NICK817.TARRY.LOCAL> select count(rownum) from t2
      2  /
    
    COUNT(ROWNUM)
    -------------
            25054
    
    appdev@NICK817.TARRY.LOCAL> delete from t2
      2  where rownum <15000
      3  /
    
    14999 rows deleted.
    
    appdev@NICK817.TARRY.LOCAL> select count(*) from t1
      2  /
    
      COUNT(*)
    ----------
         15055
    
    appdev@NICK817.TARRY.LOCAL> select count(*) from t2
      2  /
    
      COUNT(*)
    ----------
         10055
    
    appdev@NICK817.TARRY.LOCAL> select count(*) from
      2  ((select * from t1
      3  minus
      4  select * from t2)
      5  union all
      6  (select * from t2
      7  minus
      8  select * from t1))--This ensures reqd result
      9  /
    
      COUNT(*)
    ----------
          5000
    
    appdev@NICK817.TARRY.LOCAL> select count(*) from
      2  (select * from t1
      3  minus
      4  select * from t2)--While this gives reqd result
      5  /
    
      COUNT(*)
    ----------
          5000
    
    appdev@NICK817.TARRY.LOCAL> select count(*) from
      2  (
      3  select * from t2
      4  minus
      5  select * from t1-- This doesn't
      6  )
      7  /
    
      COUNT(*)
    ----------
             0
    
    appdev@NICK817.TARRY.LOCAL> drop table t1
      2  /
    
    Table dropped.
    
    appdev@NICK817.TARRY.LOCAL> drop table t2
      2  /
    
    Table dropped.
    
    appdev@NICK817.TARRY.LOCAL>
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  8. #8
    Join Date
    Sep 2002
    Posts
    376
    Hi,
    Minus returns the distinct rows returned from the first query but not from the second.
    So got to be careful when using Minus.....

    One more thing which i can think of to find the difference between two tables is Spooling both the tables into a separate text file and use OS command(diff) to findout the difference.
    I think this would be the best solution to find out the difference between the table data.

Posting Permissions

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



Click Here to Expand Forum to Full Width