MERGE - Esempio di utilizzo

Da usarsi quando è necessario un inserimento del tipo: IF EXISTS UPDATE
ELSE INSERT

SELECT AmbientID, Data, SUM(Visitors) AS SumVisitors , SUM(ViewedPages) AS SumViewedPages
INTO #temp
FROM AmbientsAccess
WHERE ZoneID in (1,2,3)
GROUP BY AmbientID, Data


MERGE INTO AmbientsAccess AS itarget
USING #temp AS isource
 ON( (itarget.ZoneID = 1 AND isource.AmbientID = itarget.AmbientID AND isource.Data = itarget.Data) )
WHEN MATCHED THEN
 UPDATE /*AmbientAccess*/ SET itarget.Visitors = isource.SumVisitors, itarget.ViewedPages = isource.SumViewedPages
WHEN NOT MATCHED THEN	-- non è più possibile usare itarget
 INSERT /*INTO AmbientAccess*/ (AmbientID, ZoneID, Data, Visitors, ViewedPages) VALUES(isource.AmbientID,1, isource.Data, isource.SumVisitors, isource.SumViewedPages);

DROP TABLE #temp