Data Reconciliation of Tables


  • I am using SQL Server 2008 R2. I need to reconcile two tables (each holding about 150K to 200K rows of data) with similar data and pick differences which is fairly easy. I just cannot come up with a query to return columns that are not for reconciling yet must be on the results. Assume table A and table B need to be reconciled with columns A, B, C, D, E. I need a resultset to show rows in A but NOT in B OR in B but not in A by virtue of comparing columns A, B, C ONLY yet returning the whole resultset (i.e A-E)
    Tuesday, November 26, 2013 1:00 PM


  • Assuming columns A, B, C cannot be null.

    select A=coalesce(A.A,B.A), B=coalesce(A.B,B.B), C=coalesce(A.C,B.C), D=coalesce(A.D,B.D), E=coalesce(A.E,B.E),

    RowMissingFromTable = case when A.A is null then 'A' else 'B' end 

    from A full outer join B

    on A.A = B.A

    and A.B = B.B

    and A.C = B.C

    where A.A is null or B.B is null

    • Marked as answer by Kylex_Desuza Wednesday, November 27, 2013 12:21 PM
    Tuesday, November 26, 2013 1:49 PM

All replies