question

pjkarthikmca avatar image
pjkarthikmca 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.
differential-backup
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Jeremy_C avatar image
Jeremy_C answered
> 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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

pjkarthikmca avatar image
pjkarthikmca answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sule avatar image
Sule answered
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)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.