INSERT pattern instead of also involving
UPDATEs can be highly problematic. IMO, it's an anti-pattern. In 99.9% of cases, it's better to use
UPDATES when possible.
INSERT pattern is implemented such that the subset of rows is blindly deleted in the target, which can create a tremendous amount of unnecessary write activity because the data is always re-written even if nothing changed.
Not only does this put load on the I/O subsystem, but it can also kill data access concurrency unless snapshot isolation is used (which will multiply the wasted disk write workload). If the default isolation level is used and the data is concurrently read, this pattern can produce deadlocks very readily and in large quantity.
Having said that, one needs to implement
UPDATEs carefully because they still have the potential to rewrite rows that haven't changed. Thankfully that's solved easily with a bit of extra code. All in all, it's well worth the effort to use
UPDATEs despite the slight added code complexity.
Depending on what you're doing, it may be easier and more reliable to send the entire desired state as a whole to the database using something like a table-valued parameter and sorting things out there instead of on the client side.