I looked around for an easier way to do this and didn't find anything that quite suited. Perhaps there isn't anything, but I thought I'd ask here, first.

The situation I have is this:

1) I have some number of tables (a dozen, or so) in a datatable.
2) Each table is different from one another, but each matches a table in a database exactly (same number of fields, same names on the fields, same names on the tables, same datatypes, same constraints).
3) The records in the datatable may or may not match records in the matching DB table, so for every record in the datatable, there will be either an INSERT or an UPDATE.
4) I don't have to worry about concurrency issues. There won't be a case where two processes will ever be working on the same record in the database.
5) This has to be in code. A stored procedure would be a significant problem (or at least I'd prefer to avoid crossing that bridge at this time).

So, the brute force approach would be to write an UPDATE statement that updated every field in the DB table (other than the primary key field) with the matching field in a datarow from the datatable, where the PK are the same. If that returns 0, then do an INSERT.

The brute force approach would require that the UPDATE and INSERT methods be generated somewhat automatically, since there are so many tables.

One option that I can see would be to create a local datatable, and use a dataadapter to populate it from the database table with all records that matched the PKs in the incoming datatable. In other words, SELECT all records from the DB table for which the PK of the record matches one of the PKs in the incoming datatable. That would give me a local datatable, I could then go through each datarow in the incoming datatable and either update the local, or add a new record if the row was new. Then UPDATE the local datatable back to the DB.

This approach has an issue, since that initial SELECT would be a bit of a chore, and this would essentially be copying the data from an existing datatable into a new one for the sole purpose of toggling the Status of the datarow appropriately. Therefore, it seems like there ought to be a better way.

One thing I have encountered is Merge. Some people argue that it shouldn't be used because of the potential for race conditions and deadlocks, but those all seem to occur as a result of concurrency issues, and I'm pretty sure I can ignore those, in this case. However, there IS some slim chance that I'd re-use the code in such a way that concurrency might, conceivably, matter. Furthermore, I have only seen MERGE examples in stored procedures, though some look like they could be used elsewhere.

So, is there a better way for my circumstances?