I need to run various aggregations against multiple tables. There can be between 1-5 aggregations per table.
I have created a metadata table to hold and maintain these rules. I then need to run obtain these aggregations and insert the data into a table.
I can dynamically generate the multiple insert statements, but is there a neat way of executing these statements.
My initial thought is that I would have to load the statements into a temp table (there will only ever 100 statements as a maximum) along with a row_number or identity
and use a while loop to execute each one in turn. I would delete the statement following the execute and subtract one from the counter until all have gone.
I generally try to avoide this iterative approach and want to keep things simple. Any thoughts?