Append tables to view
-
Hi
I need to create a view that joins a common table in each of several monthly databases, easy enough. However with each passing month a newly created monthly database's table has to be added to the view.
The question is, how do I retrieve the T-SQL for the view as stored in the previous monthly database and append the required portion of code to include the new month's table and store the view amongst the new month's database objects?
The code:
CREATE view dbo.table as
select * from Jan.dbo.table
union all
select * from Feb.dbo.table
union all
select * from Mar.dbo.table
union all
select * from Apr.dbo.table
I just need to append a portion:
union all
select * from May.dbo.table
-
select sc.text
from sysobjects so join syscomments sc on so.id = sc.id
where so.name = 'vaProctime'
-- and type = 'V'
But imho it would probably be easier to generate the whole
"ALTER VIEW dbo.table
select * from Jan.dbo.table
union all
select * from Feb.dbo.table
union all
select * from Mar.dbo.table
union all
....... etc"
from scratch and EXEC( "ALTER VIEW ..... ..." ).
/rockmoose
You must unlearn what You have learnt -
one other way would be create all 12 tables in advance, leave future ones unpopulated, then define view to always select from all 12.
if concern is that you only want to reference past data might achieve this by adding where date < getdate() or such criteria to each select.
-
Thanks so much. I've developed a script which generates the view from scratch.
All your help is much appreciated.
-
another idea might be to design/alter your tables to store all the records in a single table with a column that gives the month/year.
if you altered the tables it may be more work now but would save lots of time later.
cheers
dbgeezer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply