This is going to depend a lot on the sizes of the data (how much is in the DB, how much is in the file, how many matching rows there will likely be that need updating rather than inserting, and how many entirely matching rows that require neither insert nor update there could be) and to an extent your IO subsystem's capabilities – so any answer in the absence of a bit more detail is going to be either an educated guess or a general case that might not entirely apply.
With that in mind:
- lookup then insert or update
Unless a large amount of the input rows will result in an update (rather than an insert, or nothing) then this is likely to be the best option because the inserts can be done in bulk.
You might be able to do the updates in bulk to by inserting them into a holding table and doing the update into the target table by an
UPDATE TargetTable FROM TargetTable JOIN HoldingTable ON KeyValue=KeyValue. A hybrid of the lookup method and holding table methods. Profile this for your data though: the double write (once to holding, once to final target) and extra read may negate any performance benefit of skipping the per-update round-trip to the DB, and result in a bunch of extra transaction log activity that may affect your backups and such.
- One merge statement per input row
As you identify, this is likely to be the least efficient way by far because there will be a round-trip to the DB for every single row.
- Insert all into a holding table, merge all in one step
This may win out as simple and easy to maintain, but be careful of the effect the double-write may have on backups and IO contention generally. If the target is a DB you rebuild every time, so doesn't do log backups or anything like, and the IO impact isn't going to affect other users of this DB or other DBs on the same storage, and you aren't likely to hit some of the as yet unresolved issues with
MERGE, then go for it.
- Use both the target table and CSV as sources, merge in SSIS (suggested in Ahmed's answer)
I wouldn't recommend this generally. In some cases where the amount of data in the target table is small, this might not be terrible (and may even perform better if most of the CSV rows result in no change at all, i.e. no
UPDATE needed), but it will result in reading out the entire target table and if the SSIS package is not running local to the DB sending all that data over the network, where the lookup approach might only need to read a minority of the existing rows.
If you do go with this idea, pull the data out from the DB already in key order (in the source component
… ORDER BY CompareKey and use the advanced source editor to mark the data as sorted by that/those columns) instead. Having a SSIS sort component will force a resort in SSIS where the data should be readable in-order much more efficiently at the other side as the desired order is likely following the table's primary key order.
tl;dr: done right 1 is likely the most efficient and therefore what I'd generally recommend. But if 3 is simpler for you to write and maintain and the performance is good enough, maybe pick maintainability over performance. But the best answer depends on a few factors that we don't currently know.