How to insert most recent data

Question

Like many of you did before, I'm trying to rewrite a .NET application to use SqlBulkCopy to gain the needed performance profit when writing data to the database.

I understand SqlBulkCopy can only perform inserts and if I want to use updates as well, I'm going to need a staging table and merge the table to the permanent table. I've used MERGE in SQL before, so I guess I can figure this out.

I've seen examples using a transaction to make it extra safe...but when do you clean the staging table? Right after the MERGE statement? Something like DELETE IF EXISTS?

But what I don't understand: how does the MERGE statement know which record to merge? (assuming the transaction could fail and leave records untreated)

Example:

ID	Name	Surname	City
1	Bart	Simpson	Springfield
1	Bort	Simpson	Springfield
2	Lisa	Sampson	Springfield
2	Lisa	Simpson	Springfield
3	Homer	Simpson	Springfield

I understand you can never merge this as you don't know which records is preferred. In my case the most recent one, so I add could an extra column LastUpdatedOn that holds a timestamp as the name suspects.

But how can I tell MERGE to use the most recent row and delete the others to prevent an overwriting by older data the next run?

Or is there an other way, easier way to do this? Am I overlooking something?