asked
Difference between two records
> I've two records. One record in Main table & another logged record in Log table. Both table has same structure. Some column data has difference. I need to return those data.
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
> SELECT m.value, l.value FROM main m > JOIN log l ON m.something = > l.something WHERE NOT m.value = > l.value something has to be a value that is the same for both tables
Main Table: --------------------------------------------- Col1 Col2 Col3 Col4 1 john-edited male 25 2 Henry male 28 3 Tessy female 31 --------------------------------------------- Log Table: Col1 Col2 Col3 Col4 1 john male 26 ---------------------------------------------- Both table has difference in column data. I need to fetch all those information.
I suggest you to use CHECKSUM: CREATE TABLE MainTable ( Col1 int, Col2 VARCHAR(20), Col3 VARCHAR(20), Col4 VARCHAR(20) ) CREATE TABLE LogTable ( Col1 int, Col2 VARCHAR(20), Col3 VARCHAR(20), Col4 VARCHAR(20) ) INSERT INTO MainTable SELECT 1,'john-edited','male',25 UNION SELECT 2,'Henry','male',28 UNION SELECT 3,'Tessy','female',31 INSERT INTO LogTable SELECT 1,'john','male',26 UNION SELECT 2,'Henry','male',28 UNION SELECT 3,'Tessy','female',31 SELECT * FROM MainTable m INNER JOIN LogTable l ON m.Col1 = l.Col1 WHERE CHECKSUM(m.Col1,m.Col2,m.Col3,m.Col4) != CHECKSUM(l.Col1,l.Col2,l.Col3,l.Col4)
21 People are following this question.
