Compare two tables for difference and output to another table

Question

I have a table A contains information about a list of files.

I have another table B contains information about the same list of files, but LastModifyTime may changed.

Now I want to Compare these two tables, find all differences and input into Table C. There are chances new files exist in Table B but Table A. I could use

SELECT * FROM Table B

EXCEPT

SELECT * FROM Table A

It only gives me a list of differences. but I want Table C looks like this, contains both data from Table A and Table B on differences of LastModifyDate.

How can I do it? left join ?

Note: I need to have the result of comparison written into Table C. not just as a result of query.

you just need a join with a filter on unequal dates from the tables. — Vamsi Prabhala
– Vamsi Prabhala, Commented Apr 28, 2019 at 3:20

D-Shih · Accepted Answer · 2019-04-28 03:42:33Z

2

You can try to use Outer JOIN with Source_Last_Modify_Date from tableA not equal to Source_Last_Modify_Date from tbaleb.

SELECT a.Source_File_Name,
       a.Source_Creation_Date,
       a.Source_Last_Modify_Date 'Source_Last_Modify_Date_From_Table_A',
       b.Source_Last_Modify_Date 'Source_Last_Modify_Date_From_Table_B'
FROM [Table B] b 
LEFT JOIN [Table A] a 
ON a.Source_File_Name = b.Source_File_Name 
AND a.Source_Creation_Date = b.Source_Creation_Date
AND b.Source_Last_Modify_Date <> a.Source_Last_Modify_Date

edited Apr 28, 2019 at 3:42

answered Apr 28, 2019 at 3:23

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Root Loop Over a year ago

ok, what about if file exist in B but not in A? it wont be the case of a.name=b.name AND a.create=b.create How can I output the query results to Table C?

D-Shih Over a year ago

What's your expect to result from your case? and what's query results to Table C? mean?

Root Loop Over a year ago

I need a 3rd table contains the result of comparison between table A and B, not only existing file, but also newly created file. I scan a lot of files in a folder, lets say today i will feed table a and tomorrw I will feed table b, tomorrow there may have files been modified or newly created, so table b may contain new file records. so a.name=b.name may be not covering all cases.

Root Loop Over a year ago

To table C meaning the result of comparison will be written into Table C

D-Shih Over a year ago

I guess you can try to use LEFT JOIN or FULL JOIN to get your result

|

JBrooks · Accepted Answer · 2019-04-28 04:54:58Z

0

Sounds like you need to use the MINUS command to me. See http://www.sqltutorial.org/sql-minus/

answered Apr 28, 2019 at 4:54

JBrooks

10k2 gold badges31 silver badges32 bronze badges

Comments

Gulam Husain Ansari · Accepted Answer · 2019-04-28 06:08:43Z

0

Just little bit change in above answer because of question statement "There are chances new files exist in Table B but Table A."

SELECT b.Source_File_Name,
         b.Source_Creation_Date,
         a.Source_Last_Modify_Date 'Source_Last_Modify_Date_From_Table_A',
         b.Source_Last_Modify_Date 'Source_Last_Modify_Date_From_Table_B'
  FROM [Table B] b 
  LEFT JOIN [Table A] a 
  ON b.Source_File_Name = a.Source_File_Name 
  AND b.Source_Creation_Date = a.Source_Creation_Date
  AND b.Source_Last_Modify_Date <> a.Source_Last_Modify_Date

Might be it will helpful.

answered Apr 28, 2019 at 6:08

Gulam Husain Ansari

1651 gold badge1 silver badge13 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2019-04-28 11:57:03Z

If you also want to account for file deletion, then you need a FULL JOIN or similar mechanism. Filtering on FULL JOIN is tricky:

SELECT COALESCE(a.Source_File_Name, b.Source_File_Name) as Source_File_Name,
       COALESCE(a.Source_Creation_Date, b.Source_Creation_Date) as Source_Creation_Date,
       a.Source_Last_Modify_Date as Source_Last_Modify_Date_From_Table_A,
       b.Source_Last_Modify_Date as Source_Last_Modify_Date_From_Table_B
FROM [Table B] a FULL JOIN
     [Table A] b
     ON b.Source_File_Name = a.Source_File_Name AND
        b.Source_Creation_Date = a.Source_Creation_Date
WHERE a.Source_File_Name IS NULL OR
      b.Source_File_Name IS NULL OR
      b.Source_Last_Modify_Date <> a.Source_Last_Modify_Date;

Collectives™ on Stack Overflow

Compare two tables for difference and output to another table

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related