SQL Server Performance

Tables Comparation

Discussion in 'SQL Server 2005 General Developer Questions' started by revive, Nov 16, 2006.

  1. revive New Member

    Hi again,

    I have two simple tables with 2 fields each. I want to compare these tables and just get the entry(ies) that exists in the second table and does not exist in the first table.

    Is there a way to do that?

    Thanks,


    Jack Bower
    software developer
    IATAR Studio
    http://iatar.port5.com
  2. DilliGrg Member

    quote:Originally posted by revive

    Hi again,

    I have two simple tables with 2 fields each. I want to compare these tables and just get the entry(ies) that exists in the second table and does not exist in the first table.

    Is there a way to do that?

    Thanks,


    Jack Bower
    software developer
    IATAR Studio
    http://iatar.port5.com





    create table #test1
    (id int
    ,fname varchar(20)
    ,lname varchar(40)
    ,position char(10)
    ,Salutation varchar(20)
    )

    insert into #test1(id, fname, lname, position, salutation)
    values (1, 'Dave', 'Cooper', 'Manager', 'Sr' )

    insert into #test1(id, fname, lname, position, salutation)
    values (2, 'Mark', 'Lee', 'Supervisor', 'Jr' )


    create table #test2
    (id int
    ,fname varchar(20)
    ,lname varchar(40)
    ,position char(10)
    ,Salutation varchar(20)
    )

    insert into #test2(id, fname, lname, position, salutation)
    values (1, 'Dave', 'Cooper', 'Manager', 'Sr' )

    insert into #test2(id, fname, lname, position, salutation)
    values (2, 'Mark', 'Lee', 'Supervisor', 'Jr' )

    insert into #test2(id, fname, lname, position, salutation)
    values (3, 'Jane', 'Doe', 'Jenitor', 'Bussboy' )

    insert into #test2(id, fname, lname, position, salutation)
    values (4, 'Joe', 'John', 'Developer', 'Jr' )

    select * from #test1
    select * from #test2


    select t.id, t.fname, t.lname, t.position, t.salutation
    from #test1 o
    right join #test2 t
    ono.ID = t.ID
    where o.ID IS NULL


    id fname lname position salutation
    ----------- -------------------- ---------------------------------------- ---------- --------------------
    3 Jane Doe Jenitor Bussboy
    4 Joe John Developer Jr

    (2 row(s) affected)




    Also, look BOL for OUTER JOIN(LEFT/RIGHT/FULL).

    Thanks,
    DilliGrg
  3. Madhivanan Moderator

    or

    Select * from table2 t2 where not exists
    (select * from table1 where keycol=t2.keycol)

    Madhivanan

    Failing to plan is Planning to fail
  4. revive New Member

    Do you know if there are some tools to help do that, automaticaly, for a large number of tables and database?

    Thanks,

    Jack Bower
    software developer
    IATAR Studio
    http://iatar.port5.com
  5. thomas New Member

  6. DilliGrg Member

  7. madhuottapalam New Member

    Hi,

    You don't have to search for a third party tool. SQL 2005 has "tablediff" for data compare. You can make a customised script as u want

    Madhu
  8. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by madhuottapalam</i><br /><br />Hi,<br /><br />You don't have to search for a third party tool. SQL 2005 has "tablediff" for data compare. You can make a customised script as u want<br /><br />Madhu<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Thats really cool [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  9. revive New Member

    Sorry for this delay, about this problm.

    To make a script? Ok. sound cool, but how many objects I can compare with that script?
    I mean I want a full report about all object from a database (tables, views, triggers, roles, rules...)
    And considering that I need to manipulate large databases.

    Thanks,

    Jack Bower
    software developer
    IATAR Studio
    http://iatar.port5.com
  10. madhuottapalam New Member

    <br /><br />Hi Jack,<br /><br /><br /><br />Thread is started for a simple data comparison, see your first post <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Madhu

Share This Page