Compare 2 datasets and output delta in one TSQL statement

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

  • Where's the code?

  • 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?

  • OK, now the code is showing. Thanks.

  • 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 🙂

  • --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

  • Thanks for the script.

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

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