Good question, but I think it is being confused, including by me, with an UPSERT (as the previous posts' referral to MERGE indicates too).

This question needs more information to answer correctly: How is the action dealt with if the INSERT is not performed? An UPDATE instead? Maybe we actually want a unique constraint on the Name column to report an error if a name is attempted to be inserted twice? If we do need to update instead if the record already exists, how do we control concurrency in that case?

In high concurrency designs these and many more questions must be answered first and I completely agree a more deterministic approach would be required.

A big misconception I keep coming across is that there must be a perfect singleton solution for all situations.... but why not perfect the solution for each singleton situation? A welcome side-effect of this is that it keeps us all in jobs!