Hello, I have a database and I'm trying to get my information into ReportBuilder so that I can produce some reports. To do this, I would like to get all of my information into one table.
I have about 6 different views with similar fields, (6 fields are common across all views, there are 4 other fields that some have and some don't). I'd like to make a table that would combine all of this information.
A unique record could be identified by the combination of these fields, "DeptID", "CostPool", "CostElement"... (All of the views contain these)
Each of my views is essentially for one different CostPool. Is there an easy way to create such a table? I have no experience creating tables using queries so any advice/resources would be very much appreciated. Ideally, it would not be too difficult to refresh the data in this table from the views.
Let me know if more information is required. Thanks a lot!
asked Apr 11, 2011 at 07:53 AM in Default
You could use a UNION ALL to combine the views, ensuring that you have enough columns defined to hold all the common and non-common fields (don't use UNION as the resulting will data will have duplicates removed - which may not be what you expect, and it adds processing time)
and so on....
This could then be wrapped in a stored proc, or even as another view (urgh!) - whatever is best for your requirement
-> second edit following from OP comment
Alternatively you could create a table to hold all the valid columns and then
this approach would require you to determine up front what columns you need and their datatypes - is that the bit you are struggling with? You would also need to empty the table before 'refreshing' it.