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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy