2

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:

  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.

1

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.

| improve this answer | |
0

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.

| improve this answer | |
  • 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
0

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.

| improve this answer | |

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.