Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
colin.graham@stylo.co.uk
Starting Member
8 Posts |
 Posted - 2007-08-06 : 05:35:00
|
| Hello,I need to create a view which will include an additional table every week of our financial year.Example: Every week we create a table called Sales_YYYY_WW where YYYY is the Year and WW is the Week, so after the first week we have the table Sales_2007_01 and after the second week we have Sales_2007_01 and Sales_2007_02 etc.. These tables are created evey week and don't exist at the start of the financial year.I'd like to create a view at the beginning of each year to encompass each table evey week as it is created. If all of the tables were there at the start of the year it would be simple:CREATE VIEW VIEW_SALES_2007ASSELECT * from Sales_2007_01UNION ALLSELECT * from Sales_2007_02etc.But I don't know how to achieve this when the tables don't already exist. I don't want to have to edit the view each week to add in the new table.Can anyone out there help me? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-08-06 : 05:40:39
|
You should have one single table in the first place and not 52 tables for each year ! You would not have to face this problem at all.
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-08-06 : 13:05:45
|
| You will probably need to use Dynamic SQL. If you can, add some code into the process that creates the new table each week to recreate the view adding the new table. |
|
|
|
|
|
|
Creating Views with Variable no. Of Input Tables