Would it be possible to loop several tables and load them as one. The loop would come table 1, but then with one of the fields in that table 1 i would need to connect each time to the same table in a different database.
reason, our ERP package stories each entity in a separate database, in a special table i have all entities and their corresponding database name.
I know that i could use a union all in this, but then the entities would be hardcoded, in the dunamically way whenever a entity is added it would load without changes.
A kind a pseudo-code illustrating could be
load ent_id, ent_name, ent_db from entities;
for i = 1 to nbrofrecords(entities) do
load entitiy.end_id, field1, field2, field2 from entity.ent_db.dbo.table1
The general, not syntaxed checked, code would be something like this:
Ents: load ent_id, ent_name, ent_db from entities; FOR i = 0 to NoOfRows('Ents') - 1 LET vDb = peek('ent_db', $(i), 'Ents'); Data: LOAD ent_id, field1, field2 FROM $(vDb).dbo.table1; NEXT
The general, not syntaxed checked, code would be something like this:
Ents: load ent_id, ent_name, ent_db from entities; FOR i = 0 to NoOfRows('Ents') - 1 LET vDb = peek('ent_db', $(i), 'Ents'); Data: LOAD ent_id, field1, field2 FROM $(vDb).dbo.table1; NEXT
