asked
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
Thanks for your suggestion, I've just tried the UNION ALL with two of the views and am getting this error, which is what I got when I tried to use a bunch of LEFT OUTER JOINS: Msg 8623, Level 16, State 1, Line 2 The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information. Your suggestion of putting this data into a table is exactly what I'm trying to do... I just don't know how to go about doing it... I stumbled across the MERGE function but am not sure if that is the best.
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.
Yes, this is perfect.. thank you very much - I've just added the data from my first view and it's exactly what I need. I'm a bit slow because it's the first time I've worked with a stored procedure... but very interesting stuff. Thanks a lot! It's appreciated.
