fred rosenberger wrote:umm...My first thought (and I am by NO means an expert) is WHY?
There are thousands of reasons. A very simple example would be copying records from TRANSACTIONS to TRANSACTIONS_ARCHIVE tables (if you don't use partitioning). Strictly speaking, you'd probably move the records, not copy them. But for the sake of this question, that's the same.
Martin Vajsar wrote:The correct approach is to run one insert into ... select statement. The wrong approach is anything else
I don't entirely agree with this. For millions of rows, using
INSERT .. SELECT can prove to be a performance nightmare in databases with lots of concurrency. The reason for this is the fact that large parts of the source and target tables need to be copied into UNDO / REDO log space in order to ensure transactional integrity across this single statement. This can pretty much block the database engine in some circumstances.
A "good" way to tackle this problem would be to use PL/SQL (or T-SQL or whatever) and loop over smaller data subsets, inserting and committing them immediately, keeping UNDO space small.
Another "good" way could be to copy the records into a temporary table without indexes or constraints, releasing the source table read locks early, and moving the temporary table parts to the target table asynchronously in a separate transaction.
Martin Vajsar wrote:particularly reading the source table using ResultSet and inserting individual rows back into the target table
That, of course, is correct.