Maintaining partitioned views

[Thomas R. Hummel]

Hello,

I have a large set of data that I have set up as a partitioned view.
The view is partitioned by a datetime column and the individual tables
each represent one month's worth of data. I need to keep at least two
year's worth of data at all times, but after two years I can archive
the data. A sample of the code used is below. It is simplified for
space reasons.

My question is, how do other people maintain the database in this type
of scenario? I could create all of the tables necessary for the next
year and then go through that at the end of each year (archive tables
over two years, add new tables, and change the view), but I was also
thinking that I might be able to write a stored procedure that runs
once a month and does all three of those tasks automatically. It seems
like a lot of dynamic SQL code though for something like that.
Alternatively, I could write VB code to handle it in a DTS package.
So, my question again is, how are others doing it? Any suggestions?

Thanks!
-Tom.

CREATE TABLE [dbo].[Station_Events_200401] (
[event_time] [datetime] NOT NULL ,
[another_column] [char] (8) NOT NULL )
GO

CREATE TABLE [dbo].[Station_Events_200402] (
[event_time] [datetime] NOT NULL ,
[another_column] [char] (8) NOT NULL )
GO

CREATE VIEW Station_Events
AS
SELECT event_time,
another_column
FROM Station_Events_200401
UNION ALL
SELECT event_time,
another_column
FROM Station_Events_200402
GO

[Simon Hayes]

"Thomas R. Hummel" <to********@hotmail.com> wrote in message
news:a2**************************@posting.google.c om...

Hello,

I have a large set of data that I have set up as a partitioned view.
The view is partitioned by a datetime column and the individual tables
each represent one month's worth of data. I need to keep at least two
year's worth of data at all times, but after two years I can archive
the data. A sample of the code used is below. It is simplified for
space reasons.

My question is, how do other people maintain the database in this type
of scenario? I could create all of the tables necessary for the next
year and then go through that at the end of each year (archive tables
over two years, add new tables, and change the view), but I was also
thinking that I might be able to write a stored procedure that runs
once a month and does all three of those tasks automatically. It seems
like a lot of dynamic SQL code though for something like that.
Alternatively, I could write VB code to handle it in a DTS package.
So, my question again is, how are others doing it? Any suggestions?

Thanks!
-Tom.

CREATE TABLE [dbo].[Station_Events_200401] (
[event_time] [datetime] NOT NULL ,
[another_column] [char] (8) NOT NULL )
GO

CREATE TABLE [dbo].[Station_Events_200402] (
[event_time] [datetime] NOT NULL ,
[another_column] [char] (8) NOT NULL )
GO

CREATE VIEW Station_Events
AS
SELECT event_time,
another_column
FROM Station_Events_200401
UNION ALL
SELECT event_time,
another_column
FROM Station_Events_200402
GO

I would use a DTS package - the first steps to create the objects
dynamically, then later steps to call stored procedures to actually move the
data. But that's more or less a personal preference, and you could do
everything in a stored procedure as well. Assuming there are no major
reasons to use one solution or another, it probably depends on what is most
transparent and easiest to maintain for you and your organization.

Simon

[Jason Sauer]

Without dynamic SQL the view will be a problem, but I think you can easily
handle the archiving.

Within a stored procedure you can create a new copy of your table "template"
like this:

CREATE TABLE [dbo].[Station_Events_Template] ( [event_time] [datetime] NOT
NULL , [another_column] [char] (8) NOT NULL )

Then insert the rows into this table for the appropriate time period.

INSERT INTO Station_Events_Template
SELECT * FROM [your "active" table] WHERE event_time BETWEEN @thisdate AND
@thatdate

After the insert you can rename the template using the sp_rename procedure.
You will need a variable for the new table name first:
DECLARE @NewName varchar(25)

SELECT @NewName = 'Station_Events_' + CONVERT(char(6), @MonthEndDate,
112) -- returns date in format "YYYYMM"

EXEC sp_rename 'Station_Events_Template', @NewName

Hope this is helpful.

"Thomas R. Hummel" <to********@hotmail.com> wrote in message
news:a2**************************@posting.google.c om...

Hello,

I have a large set of data that I have set up as a partitioned view.
The view is partitioned by a datetime column and the individual tables
each represent one month's worth of data. I need to keep at least two
year's worth of data at all times, but after two years I can archive
the data. A sample of the code used is below. It is simplified for
space reasons.

My question is, how do other people maintain the database in this type
of scenario? I could create all of the tables necessary for the next
year and then go through that at the end of each year (archive tables
over two years, add new tables, and change the view), but I was also
thinking that I might be able to write a stored procedure that runs
once a month and does all three of those tasks automatically. It seems
like a lot of dynamic SQL code though for something like that.
Alternatively, I could write VB code to handle it in a DTS package.
So, my question again is, how are others doing it? Any suggestions?

Thanks!
-Tom.

CREATE TABLE [dbo].[Station_Events_200401] (
[event_time] [datetime] NOT NULL ,
[another_column] [char] (8) NOT NULL )
GO

CREATE TABLE [dbo].[Station_Events_200402] (
[event_time] [datetime] NOT NULL ,
[another_column] [char] (8) NOT NULL )
GO

CREATE VIEW Station_Events
AS
SELECT event_time,
another_column
FROM Station_Events_200401
UNION ALL
SELECT event_time,
another_column
FROM Station_Events_200402
GO

[Thomas R. Hummel]

Thank you both for your suggestions! I always seem to overlook using
sp_rename as part of my bag of tricks. I'll give that some
consideration, but right now I'm leaning towards a DTS package using
VB to generate the SQL code.

Thanks,
-Tom.