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.