I am developing a report in report builder 3.0 where I am extracting data from a table
The problem is this table is archived on monthly basis to a separate database (HN_Backup), and I need to combine the data from all archived tables and tables which will be generated in future and give the results. I have 2 approaches to do it:
- union all tables - the issue will be for archive tables in future as 'if exist' will not work with union.
- insert every new row added in
odcallsto a different table with only insert/update (not delete).
I would avoid using triggers or replication for this.
I can't make changes to the current structure. I was wondering what would be the best way to move every new entry in
odcalls to another aggregated table, from where I will run my report.
Please let me know if there is any workaround.