Compare 2 datasets and output delta in one TSQL statement

  • Theo Ekelmans

    SSCarpal Tunnel

    Points: 4482

    Comments posted to this topic are about the item Compare 2 datasets and output delta in one TSQL statement

  • macrocharlie

    Ten Centuries

    Points: 1055

    Where's the code?

  • tsceurman

    Old Hand

    Points: 366

    Any idea on how well this scales, or its performance versus using a full join between the two tables with a WHERE clause where either side is NULL?

  • macrocharlie

    Ten Centuries

    Points: 1055

    OK, now the code is showing. Thanks.

  • Theo Ekelmans

    SSCarpal Tunnel

    Points: 4482

    Hi,

    As with any unbalanced compare, the performance varies.

    Just compare this method with use your favorite compare and look closely at the execution plan

    In my query close to 50% of the time is spent reading the 2 source tables using a clustered index scan, which is the most efficient way to read the ENTIRE table content.

    The Merge join that does the actual compare work is only 19 % of the time

    The rest: 33%, is spent storing the result.

    If you find a more efficient way, i'd be happy to review your code!

    Theo 🙂

  • Mitesh Oswal

    SSCrazy

    Points: 2798

    --Compareandinsertdeltaintotable,inonesqlstatement

    Insert into #cmpAB

    ([EmpNr_a],[EmpNr_b],

    [Name_a],[Name_b],

    [HireDT_a],[HireDT_b],

    [FireDT_a],[FireDT_b]

    )

    SELECT a.[EmpNr],b.[EmpNr],a.[Name] , b.[Name],

    a.[HireDT] , b.[HireDT] , a.[FireDT] , b.[FireDT]

    FROM #tblA a INNER JOIN #tblB b

    ON a.[EmpNr] = b.[EmpNr]

    WHERE( a.[Name] <> b.[Name] OR a.[HireDT] <> b.[HireDT] OR a.[FireDT] <> b.[FireDT])

    UNION

    SELECT a.[EmpNr],b.[EmpNr],a.[Name] , b.[Name],

    a.[HireDT] , b.[HireDT] , a.[FireDT] , b.[FireDT]

    FROM #tblA a FULL OUTER JOIN #tblB b

    ON a.[EmpNr] = b.[EmpNr]

    where (a.[EmpNr] IS NULL OR b.[EmpNr] IS NULL)

    --Thanks

    Mitesh Oswal

    Regards,
    Mitesh OSwal
    +918698619998

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply