How to get columns data which are having different values after comparing with other table?

I have two Tables say Table-A and Table-B having same table structure (means number of columns are same) and having same number of Records with same Primary key val开发者_如何学运维ue.

It is having Column-Id data is same in both the tables but some columns values are Different.

Now I want a Select query which will return those columns only which are having different data in Tables-A and Table-B

Example :

Table-A
ID  Name   RollNo  Status
1   Gourav 22      1

Table-B
ID  Name   RollNo  Status
1   Gourav 24      0

OUTPUT Required is :

Table-C
Id  RollNo-A  RollNo-B  sts-A  sts-B
1   22        24        1      0

Please suggest. Please provide Solution in Sql 2000

This might work.

SELECT      A.Id
        ,   A.RollNo AS [RollNo_A]
        ,   B.RollNo AS [RollNo_B]
        ,   A.Status AS [Status_A]
        ,   B.Status AS [Status_B]
FROM        dbo.TableA  A
INNER JOIN  dbo.TableB  B
ON          A.Id        =  B.Id
WHERE       A.RollNo    <> B.RollNo
OR          A.Status    <> B.Status

Just do a inner join if you are sure both all IDs must be present in both the tables. If not use left outer join with the left table being the parent table. Try this:

select a.id, a.rollno [rollno-A], b.rollno [rollno-B], a.status as [sts-A], 
b.status as [sts-B]
from @tableA A
INNER JOIN
@tableB B
ON a.id = B.id