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!

more ▼

asked Apr 11, 2011 at 07:53 AM in Default

TelepathicSheep gravatar image

47 4 4 5

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.
Apr 11, 2011 at 08:26 AM TelepathicSheep
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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)


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


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.

more ▼

answered Apr 11, 2011 at 07:59 AM

Kev Riley gravatar image

Kev Riley ♦♦
54k 47 49 76

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.
Apr 11, 2011 at 09:16 AM TelepathicSheep
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 11, 2011 at 07:53 AM

Seen: 1966 times

Last Updated: Apr 11, 2011 at 07:53 AM