SQL Server UPSERT

Upsert.sql

MERGE CasualRosterAllocation AS D
			USING 
			(
				SELECT x.Id, Date, Shift.Id AS ShiftId, Casual.Id AS CasualId, PlaceHolderCount,
					(SELECT MAX(RosterAllocationHeader.Id) 
					 FROM RosterDetail 
						INNER JOIN RosterAllocationHeader ON RosterAllocationHeader.RosterId = RosterDetail.Id
					 WHERE RosterDetail.Guid=RosterGuid) AS RosterAllocationHeaderId
				FROM (VALUES (@0, @1, @2, @3, @4, @5)) x (Id, RosterGuid, Date, ShiftGuid, CasualGuid, PlaceHolderCount)
					INNER JOIN Shift ON Shift.Guid = ShiftGuid
					LEFT JOIN Casual ON Casual.Guid = CasualGuid
			) AS S 
			ON 
				D.RosterAllocationHeaderId=S.RosterAllocationHeaderId 
				AND D.Date=S.Date 
				AND D.ShiftId=S.ShiftId 
				AND D.CasualId IS NULL AND S.CasualId IS NULL
			WHEN MATCHED 
			THEN
				UPDATE SET
				D.PlaceHolderCount=D.PlaceHolderCount+S.PlaceHolderCount
			WHEN NOT MATCHED
			THEN
				INSERT (Id, Date, ShiftId, CasualId, RosterAllocationHeaderId, PlaceHolderCount)
				VALUES (S.Id, S.Date, S.ShiftId, S.CasualId, S.RosterAllocationHeaderId, S.PlaceHolderCount)
			;