Identifying the difference between 2 tables

[gatetec]

I have this query and works fine. The output shows the different records between two tables. Is there a way to know what is different in each record? Sometimes, the output returns over 1,000 records. It is not possible to go over all columns of each record to compare what has been changed. If there there is another query to show the emp ID (A.employee) and the only different contents of each column of each record, that will be great.

Please advise.

select A.*
from TP2000 A
left join TP2000Prev B
on B.employee = A.employee
where
NOT (B.employee = A.employee and
B.firstname = A.firstname and
B.middlename = A.middlename and
B.lastname = A.lastname and
B.addr1 = A.addr1 and
B.addr2 = A.addr2 and
B.city = A.city and
B.state = A.state and
B.zip = A.zip and
B.hmphonenbr = A.hmphonenbr and
B.datehired = A.datehired and
B.termdate = A.termdate and
B.department = A.department and
B.departmen2 = A.departmen2 and
B.jobcode = A.jobcode and
B.jobdescrip = A.jobdescrip and
B.empstatus = A.empstatus and
B.emergconta = A.emergconta and
B.emergphone = A.emergphone)
OR b.employee is null

 

[gmmastros]

You could try using the NullIf function. With NullIf, if both parameters are the same value, it returns NULL. So...

[tt][blue]
Select NullIf(A.ColumnName, B.ColumnName) As ColumnName,
NullIf(A.NextColumn, B.NextColumn) As NextColumn
[/blue][/tt]

So, if the values are the same, you will see NULL. Otherwise, you will see the value in the column. If both columns are already null, you will still see null. Most of the time, you will probably see the data in the A table, but if the value in the A table is null, you will see the value from the B table instead.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gatetec]

ok. so, do I need to put below without WHERE clause?

from TP2000 A
left join TP2000Prev B
on B.employee = A.employee

Thanks much

 

[gatetec]

select
nullif(B.firstname ,  A.firstname) as a,
nullif(B.middlename ,  A.middlename) as b,
nullif(B.lastname ,  A.lastname) as c,
nullif(B.addr1 ,  A.addr1) as d,
nullif(B.addr2 ,  A.addr2) as e,
nullif(B.city ,  A.city) as f,
nullif(B.state ,  A.state) as g,
nullif(B.zip ,  A.zip) as h,
nullif(B.hmphonenbr ,  A.hmphonenbr) as i,
nullif(B.datehired ,  A.datehired) as j,
nullif(B.termdate ,  A.termdate) as k,
nullif(B.department ,  A.department) as l,
nullif(B.departmen2 ,  A.departmen2) as m,
nullif(B.jobcode ,  A.jobcode) as n,
nullif(B.jobdescrip ,  A.jobdescrip) as o,
nullif(B.empstatus ,  A.empstatus) as p,
nullif(B.emergconta ,  A.emergconta) as q,
nullif(B.emergphone ,  A.emergphone) as r
from TP2000 A
left join TP2000Prev B
on B.employee = A.employee

This returns an error:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ' '.

 

[SQLSister]

Query is syntactically correct in my query analyzer. Check the spelling of each of the field names in each table.

"NOTHING is more important in a database than integrity." ESquared

 

[gatetec]

That is odd!!!
All field names in each table are correct though.