Overhead of decomposing complex query into multiple simple queries?
As a general rule of thumb, you should give the database as much information as possible about the task that you're implementing. How does this apply to your scenarios?

Scenario 1 (INSERT .. SELECT)

The database knows that you're about to bulk-move a whole set of data from one table or from one derived table to another. It can optimise the execution given:


The cardinality of the bulk operation
The various constraints / triggers that will be invoked
The amount of storage that needs to be reserved in the new table
etc etc.


Scenario 2 (SELECT, and the N x INSERT)

The database has no clue about the various prepared statements that you are going to send after the SELECT. Even if it were smart enough to collect long-term statistics and heuristics about what's going to come after your SELECT, it would be unwise to assume anything about the subsequent load. So, effectively, this scenario is most often much much worse than the other.

There are some remarks to place, though:


Even if you have a good application-design reason to partition INSERTs like that, you should then probably batch-send them to the database
One good reason to partition INSERTs is the fact that you can have more fine-grained control over transaction lengths. It is generally not good to insert millions of records in a long-running transaction with logging turned on. So, either turn off logging, or commit after N inserts


Conclusion

The above are remarks regarding your two specific scenarios. Real-world scenarios aren't that simple, but you've also stated that in the question. The point I'm trying to make is that in many cases, you should let the database perform bulk data operations, because that's what it's very good at.