What are the best practices for ETL of data from one OLTP system to another, Part 2? Ok, I originally started a question a ways back called "What are the best practices for ETL of data from one OLTP system to another?". And as you can probably tell from most of my questions so far, they have to deal with ETL. This is because as a developer I am seeking knowledge from those much closer to databases than I am. Also I didn't want to muddy the waters on the question I originally asked, since I did get an answer to that question. Hence, my "part 2".

With that said, I am probably going to be using custom Java code per high level design review with the architects and other key stakeholders. Now I am on to the detailed design for the ETL process. What I am wondering is what is the best way for me to insert/update the data in the target database?

Should I just use standard code looping techniques and insert/update record-by-record as I process them? Or is it better to generate a .sql flat file of all changes and then execute that at the end? Since I need to acquire target DB record keys for a key mapping database for my ETL I'm not sure the second choice is the best idea?

Any ideas? Guidelines? Help? What are the pros and cons of each? Are there any requirements (functional or non) that help you decide which option? Any help here with me understanding what I'm up against is appreciated.
Relational database guys always like to think in "sets" but some tasks just don't work that way.  With ETL, since there is no way around the physical disk having to actually write data you just want to reduce the overhead related to each individual query as much as possible.  

Speaking for MSSQL.  If you use "parameterized" queries.  Looping in code and updating in that fashion should be fine.  The reason for this is that by using a parameterized query, the SQL engine can cache a query plan to use for each iteration of your loop and avoid the overhead of starting from scratch each time.  

Google parameterized query if you are unfamiliar with that term.  Speaking in Java terms you can also search for Prepared Statements.

I say "should be fine" in my previous post.... assuming that the query you are going to run repeatedly is as well written and efficient as possible.  

If you are able to analyze a sample query that is going to be run repeatedly and find out what it's resulting query plan is.  You may find there are indexes that aren't necessary that can be turned off in order to improve the performance of the update.  

Or, on the flip side, if your update includes logic that causes a table scan on a related table you may find the addition of an index would greatly improve the performance of your update.