Extracting Data from multiple archive tables I am developing a report in report builder 3.0 where I am extracting data from a table dbo.odcalls.
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 odcalls to 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.
This is a pretty good case for Distributed Partitioned Views.

It would be fairly easy to implement, especially if the archives have the same schema.