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 seperate 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:

  1. union all tables - the issue will be for archive tables in future as 'if exist' will not work with union.
  2. 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 for the same.


What if you go with option 1, putting the query in a database view and have the process which archives the table alter the view to append a new union including the new table? That way each month the data will be archived and your view automatically updated to utilize the new table.

I'm not sure how well this whole thing will work out performance wise, but it seems like this solution could do what you're asking.

  • As a minor disagreement, I suggest that the process which creates a series of archive tables in [HN_Backup] is made to create a view in the form of "SELECT * FROM [odcalls_2017_01] UNION ALL SELECT * FROM [odcalls_2017_02] UNION ALL ... " etc. But with column names. INFORMATION_SCHEMA.TABLES provides table names. This avoids tying this view to your actual report query. Alternatively, you create the view yourself, in tempdb if there is no other way. You can take the current table first and then add "UNION ALL" for each archive table. – Robert Carnegie May 29 '17 at 11:16

If the names of the future tables follow a rigid naming convention and are predictable, then union all can be achieved with dynamic SQL.

One drawback I see in this method is when/if an accidental addition of tables with similar names (whether for testing or by mistake) happen.


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.


You also can create a stored procedure with variables to create a view. Dynamically create the views and then union inside the stored proc should let you move archived data as I think you want out of the tables in question

  • Archiving data is not the same as taking backups. Data that is no longer meant to be changed may be transferred to a different – usually read-only – repository to reduce the size of the original table for more light-weight OLTP. We have something similar in one of our project. And it's only for that reason that I might understand what you mean with your suggestions. But I'm not entirely sure and others might not understand this at all, so perhaps you could elaborate, possibly with an example. – Andriy M Apr 23 '18 at 9:59
  • Also, please avoid questions in your answers. This is a Q&A site, not a forum, we don't do discussions, we just provide answers to questions. When you want to clarify something from the OP, you can post a comment on the question once you've got enough rep (which will be deleted once it's served its purpose). Other than that, welcome to the site! – Andriy M Apr 23 '18 at 10:04
  • I’m not sure why you feel the need to go into detail negatively rather than say thanks. Instead you criticized my input. I’m not doing a qa at all here. I just offered an option. If you focused the energy on two elaborating comments that would be more helpful than lecturing me on my participation and helping. Please avoid telling people how to contribute in their own way. – Webmaster TheCMG Apr 24 '18 at 1:38
  • 1
    I apologise if I came across as overly criticising, that wasn't the intention, I assure you. Also, not to criticise but to clear up possible confusion, I was speaking about Q&A (questions and answers), not QA (quality assurance). If you got me right the first time around, I apologise again, and wish you a good day. – Andriy M Apr 24 '18 at 6:04
  • 2
    And i really don't get what's wrong with criticism. You posted an answer on a site that is viewed by thousands if not millions every day. You published something for the whole world to see and you don't expect criticism? The site's purpose is to provide answers to questions and solutions to problems. We all just want to have the best possible answers/solutions, and criticism only helps to improve them. It's not directed to you anyway, it's directed to the answer. – ypercubeᵀᴹ May 2 '18 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.