Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Compare 2 tables

Answered Compare 2 tables

  • Wednesday, February 13, 2013 9:22 AM
     
     
    Sign In to Vote
    0

    Hi, i have 2 tables in my Database

    Table1 has the columns

    Servername - ip_address - status

    Table2 has the columns

    Servername - ipaddress

    I would like to compare both tables and query for servername that are in Table2(servername) but are not in Table1(servername)

    How can i achieve this?

    Thanks

     

All Replies

  • Wednesday, February 13, 2013 9:26 AM
     
     Answered
    Sign In to Vote
    1

    select servername 

    FROM table1 a 

    where  NOT EXISTS (SELECT servername from table2 b where a.servername = b.servername)

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh


    • Edited by Kapil_KK Wednesday, February 13, 2013 9:30 AM
    • Proposed As Answer by Kapil_KK Wednesday, February 13, 2013 9:30 AM
    • Marked As Answer by slowscripter Wednesday, February 13, 2013 9:59 AM
    •  
     
  • Wednesday, February 13, 2013 9:29 AM
     
     Proposed Answer Has Code
    Sign In to Vote
    1

    Are you looking for the below:

    Drop table T1,T2
create Table T1(servername Varchar(20),ip Varchar(20), status bit)
Insert into T1 Select 'server1','213.234.234.234',1
Insert into T1 Select 'server10','123.123.234.234',1
create Table T2(servername Varchar(20),ip Varchar(20))
Insert into T2 Select 'server1','213.234.234.234'
Insert into T2 Select 'server100','234.456.456.345'
Select T2.* From T2
Left Join T1 On T2.servername = t1.servername 
Where T1.servername is null

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed As Answer by Junaid_Hassan Wednesday, February 13, 2013 9:51 AM
    •  
     
  • Wednesday, February 13, 2013 9:32 AM
     
     Answered
    Sign In to Vote
    1

    SELECT servername 

    FROM table2 a 

    where  NOT EXISTS (SELECT servername from table1 b where a.servername = b.servername)

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    • Marked As Answer by slowscripter Wednesday, February 13, 2013 9:59 AM
    •  
     
  • Wednesday, February 13, 2013 10:00 AM
     
     
    Sign In to Vote
    0

    thanks for the query it worked fine.

    Regards

     
  • Wednesday, February 13, 2013 2:46 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0
     
    SELECT servername FROM   table2
EXCEPT
SELECT servername FROM   table1