I have a target table of about 2000 rows and I want to be able to MERGE (with HOLDLOCK) about 5-10 rows at a time into the target table. The target table doesn't get used heavily, with only 1-2 users inserting or updating data infrequently.
Now, I already have a stored procedure that I use to update the rows in the source table, one row at a time. It would be much simpler to include the MERGE logic in this stored procedure instead of having a separate stored procedure to move everything at once.
Given the usage, I don't see this being an issue, but I was curious what kind of overhead or problems can arise from using MERGE and HOLDLOCK for rows one at a time.