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