Than, why do you need a code to iterate the tables? It does not make a difference iterating the tables constructing dynamic insert-statements and execute them compared to writing insert-statement for each table and make the inserts into the Fact table.

Best practices for inserting data in a datawarehouse is developing a SSIS package. There are many advantages of using SSIS packages. SSIS is developed for that goal. But if you don't have experience with SSIS, then you can write insert-statement codes and schedule them with a job.