How do I update and insert specific set of rows to an existing table using Merge in SQL Server 2008 R2?
A simpler way to write the MERGE is as follows:

MERGE #target /* WITH (SERIALIZABLE) */ AS T
USING #source AS S
    ON S.Id = T.Id
WHEN MATCHED
    AND T.IsDeleted = 0
    AND S.Modified &gt; T.Modified
    THEN UPDATE SET
        Modified = S.Modified,
        NoteText = S.NoteText
WHEN NOT MATCHED THEN
    INSERT (Id, Modified, IsDeleted, NoteText)
    VALUES (S.Id, S.Modified, 0, S.NoteText);


The SERIALIZABLE hint would be necessary to avoid a potential race condition. This consideration obviously does not apply to a local temporary table target, but it would be remiss of me not to mention.

In broad terms, it is almost always wrong to perform any sort of filtering in the ON clause (or equivalently, in a CTE). The ON clause should only be used to specify how rows between the source and target are related.

See Inserting, Updating, and Deleting Data by Using Merge for more details.

The source and target tables should also have a unique index or key. Moreover, for best performance SQL Server should be able to guarantee that the MERGE statement will affect each target row a maximum of once. Without that, the execution plan will have to detect multiple DML operations on the same row in order to raise a runtime error if it happens. This often requires a Sort, and always requires a Segment, Sequence Project, and Assert. None of these will make your command execute any faster :)