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 |
|
Kuda
Starting Member
17 Posts |
 Posted - 2002-08-20 : 15:16:46
|
| I'm working on integrating two different applications that both use MSSQL. Unfortunately one of the products has some problems with the database design - namely that for certain tables, each year the table gets re-created with the year as part of the table name, i.e. if the base name was HISTORY, there might be tables called HISTORY99, HISTORY00, HISTORY01, and HISTORY02 in the database.What I need to do is to create a view that will include all of the tables with a common base name. I realize I could do this by using something like:CREATE VIEW [HISTORY] ASSELECT * FROM HISTORY99UNION ALLSELECT * FROM HISTORY00etc. etc. etc.The thing is, I would then have to go back each year and modify the view to account for the new year's table. What I would like to do instead is figure out some way of dynamically including all the tables with the same base name, so that if/when HISTORY03 gets created, it would automatically be accounted for in the view.I've managed to come up with some dynamic SQL that returns the result set I want, but I can't create a view that makes use of dynamic SQL. Then I got sneaky and thought that I'd wrap that code in a stored proc. and base the view on the results of that, but found I couldn't do that either...Does anyone have any suggestions?TIA,JohnBut who can save the sane? Some beings just can't change... |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-20 : 16:48:29
|
What you REALLY should do is put all of the data into one table, if you don't want to have to update it every year.  Having said that, having to change the view once a year isn't all that burdensome. Without redesigning the databases that's the best you can do. |
 |
|
|
Kuda
Starting Member
17 Posts |
Posted - 2002-08-21 : 08:55:21
|
| I agree 1000000000% :) Too bad the app I'm working with doesn't though.So there's no way to dynamically include a table in a view huh? Color Kuda an unhappy camper...But who can save the sane? Some beings just can't change... |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-08-22 : 08:16:09
|
| Unfortunately, it sounds like you are stuck. If you don't want to run the ALTER VIEW script you write every year, then why not write a SQL Job to do the work for you. Simply write your procedure to check and see if the new table exists. If so, run your alter view to Union the new table to the view. You will have to use some dynamic SQL with the SP, but it should work. Once you have your procedure written, you can set up a job to run once a year on a date you know the new table should be there. You might want to write some code to run the job more than once if it doesn't find the new table.Good Luck!Jeremy |
|
|
|
|
|
|
|
|
Dynamic View Definition