CODEWITHSUNDEEP
sqlsql-server
Root Loop
Root Loop

Reputation: 3162

Compare two tables for difference and output to another table

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

enter image description here

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

enter image description here

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.

enter image description here

Upvotes: 0

Views: 147

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

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;

Upvotes: 0

Gulam Husain Ansari
Gulam Husain Ansari

Reputation: 165

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.

Upvotes: 0

JBrooks
JBrooks

Reputation: 10013

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

Upvotes: 0

D-Shih
D-Shih

Reputation: 46239

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

Upvotes: 2

Related Questions