want to create table, from multiple views - where primary key will be several fields?
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! Sheep
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) e.g. select DeptID, CostPool, CostElement, col1, col2, '' as col3, '' as col4 from View1 union all select DeptID, CostPool, CostElement, '' as col1, '' as col2, col3, col4 from View2 and so on.... This could then be wrapped in a stored proc, or even as another view (urgh!) - whatever is best for your requirement -> Edit : just re-read your question, and you want to get this into a table - so simply insert this data into your new table. **-> second edit following from OP comment** Ok so it sounds like the views are complex views, probably relying on lots of underlying tables and possibly more views. You could try and source the data from the view definition, rather than relying on the view itself? Alternatively you could create a table to hold all the valid columns and then insert into newTable select DeptID, CostPool, CostElement, col1, col2, '' as col3, '' as col4 from View1 insert into newTable select DeptID, CostPool, CostElement, '' as col1, '' as col2, col3, col4 from View2 etc... 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.