SQLServerCentral / Article Discussions / Discuss content posted by Theo Ekelmans / Article Discussions by Author / Compare 2 datasets and output delta in one TSQL statement / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 04:11:08 GMT20RE: Compare 2 datasets and output delta in one TSQL statementhttp://www.sqlservercentral.com/Forums/Topic1200330-596-1.aspx[code="sql"]--Compareandinsertdeltaintotable,inonesqlstatementInsert 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 bON a.[EmpNr] = b.[EmpNr]WHERE( a.[Name] &lt;&gt; b.[Name] OR a.[HireDT] &lt;&gt; b.[HireDT] OR a.[FireDT] &lt;&gt; 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 bON a.[EmpNr] = b.[EmpNr]where (a.[EmpNr] IS NULL OR b.[EmpNr] IS NULL)--Thanks Mitesh Oswal [/code]Sun, 27 Nov 2011 21:34:28 GMTMitesh OswalRE: Compare 2 datasets and output delta in one TSQL statementhttp://www.sqlservercentral.com/Forums/Topic1200330-596-1.aspxHi,As with any unbalanced compare, the performance varies.Just compare this method with use your favorite compare and look closely at the execution planIn 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 timeThe rest: 33%, is spent storing the result.If you find a more efficient way, i'd be happy to review your code!Theo :)Sun, 06 Nov 2011 08:52:50 GMTTheo EkelmansRE: Compare 2 datasets and output delta in one TSQL statementhttp://www.sqlservercentral.com/Forums/Topic1200330-596-1.aspxOK, now the code is showing. Thanks.Fri, 04 Nov 2011 05:51:06 GMTmacrocharlieRE: Compare 2 datasets and output delta in one TSQL statementhttp://www.sqlservercentral.com/Forums/Topic1200330-596-1.aspxAny 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?Fri, 04 Nov 2011 05:36:15 GMTtsceurmanRE: Compare 2 datasets and output delta in one TSQL statementhttp://www.sqlservercentral.com/Forums/Topic1200330-596-1.aspxWhere's the code?Fri, 04 Nov 2011 05:12:55 GMTmacrocharlieCompare 2 datasets and output delta in one TSQL statementhttp://www.sqlservercentral.com/Forums/Topic1200330-596-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/T-SQL/76689/">Compare 2 datasets and output delta in one TSQL statement</A>[/B]Thu, 03 Nov 2011 21:02:45 GMTTheo Ekelmans