Newby Question: Looping Over Rows

I imagine a data-retrieval scenario where data destined for several different tables are collected and initially placed into one 'collector' table (say, ALLDATA); a stored procedure then kicks in and passes the records to individual destinations. For example, I might want to have data for two persons, Ann and Betty, come in and be routed to table_ann and table_betty respectively, with the correspondence stored in table LOOKUP:

id output_table
Ann table_ann
Betty table_betty

How could this be implemented? Perhaps with some kind of loop over the rows of LOOKUP, with table name retrieved and entered into a (EXEC-written?) query

insert into <table_name>
from alldata
where id in (select distinct id
from lookup
where output_table = <table_name>)

But how could one iterate over the list of table names? (These could be placed in table, of course, and the question become 'how could one iterate over a table's rows?')

I hope that this is an easy question for SQL Server pros, and will be grateful for a brief pointer/code snippet. (I may have given more background then necessary, but perhaps there are better solutions available there as well?)

Thanks a lot!