November 3, 2011 at 9:02 pm
Comments posted to this topic are about the item Compare 2 datasets and output delta in one TSQL statement
November 4, 2011 at 5:12 am
Where's the code?
November 4, 2011 at 5:36 am
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?
November 4, 2011 at 5:51 am
OK, now the code is showing. Thanks.
November 6, 2011 at 8:52 am
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 🙂
November 27, 2011 at 9:34 pm
--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
May 12, 2016 at 6:32 am
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