Unix Technical Forum

Help: Purge Table Stored Procedure

This is a discussion on Help: Purge Table Stored Procedure within the SQL Server forums, part of the Microsoft SQL Server category; --> I have to create a stored procedure to purge "x" # of records from a table. I have two ...


Go Back   Unix Technical Forum > Database Server Software > Microsoft SQL Server > SQL Server

Help: Purge Table Stored Procedure Help: Purge Table Stored Procedure

Register FAQ Members List Calendar Search Today's Posts Mark Forums Read

Reply

 

LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 02-29-2008, 02:35 AM
Dan Caron
 
Posts: n/a
Default Help: Purge Table Stored Procedure
I have to create a stored procedure to purge "x" # of records from a
table. I have two tables (script below):

Schedule
ScheduleHistory

I need to purge records out of ScheduleHistory. The problem is that
the # of records that needs to be "kept" is dynamic, and stored in the
Schedule table. So all records in ScheduleHistory should be purged,
except for the most recent "x" number of records.

For instance, for each Schedule row, you can specify how many records
to "keep" at all times. I want to keep the most recent "x" # of
ScheduleHistory rows for each Schedule.

In the script below, I have two schedules ("test1" and "test2"). One
of them specifies that the most recent "5" records in ScheduleHistory
should be kept, and the other specifies that the most recent "3"
records in ScheduleHistory should be kept.

So, I need a stored procedure that can clean up this table on demand.
I can't figure this one out...it's over my head.

Dan

-----------------
SCRIPT BELOW
-----------------


CREATE TABLE [dbo].[Schedule] (
[ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
[TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[TimeLapse] [int] NOT NULL ,
[TimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetryTimeLapse] [int] NOT NULL ,
[RetryTimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetainHistoryNum] [int] NOT NULL ,
[ObjectDependencies] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ScheduleHistory] (
[ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
[ScheduleID] [int] NOT NULL ,
[StartDate] [datetime] NOT NULL ,
[EndDate] [datetime] NULL ,
[Succeeded] [bit] NULL ,
[LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NextStart] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #2 (permalink)  
Old 02-29-2008, 02:36 AM
Dan Guzman
 
Posts: n/a
Default Re: Purge Table Stored Procedure
Assuming you want the keep the most recent rows based on StartDate, one
method:

DELETE FROM ScheduleHistory
FROM Schedule s
WHERE
(
SELECT COUNT(*)
FROM ScheduleHistory sh
WHERE
sh.ScheduleID = ScheduleHistory.ScheduleID AND
sh.ScheduleID = s.ScheduleID AND
sh.StartDate >= ScheduleHistory.StartDate
) > s.RetainHistoryNum

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dan Caron" <dancaron-listserv0687@mailblocks.com> wrote in message
news:4d6e6a6c.0404231047.60cd40d0@posting.google.c om...
> I have to create a stored procedure to purge "x" # of records from a
> table. I have two tables (script below):
>
> Schedule
> ScheduleHistory
>
> I need to purge records out of ScheduleHistory. The problem is that
> the # of records that needs to be "kept" is dynamic, and stored in the
> Schedule table. So all records in ScheduleHistory should be purged,
> except for the most recent "x" number of records.
>
> For instance, for each Schedule row, you can specify how many records
> to "keep" at all times. I want to keep the most recent "x" # of
> ScheduleHistory rows for each Schedule.
>
> In the script below, I have two schedules ("test1" and "test2"). One
> of them specifies that the most recent "5" records in ScheduleHistory
> should be kept, and the other specifies that the most recent "3"
> records in ScheduleHistory should be kept.
>
> So, I need a stored procedure that can clean up this table on demand.
> I can't figure this one out...it's over my head.
>
> Dan
>
> -----------------
> SCRIPT BELOW
> -----------------
>
>
> CREATE TABLE [dbo].[Schedule] (
> [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [TimeLapse] [int] NOT NULL ,
> [TimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetryTimeLapse] [int] NOT NULL ,
> [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetainHistoryNum] [int] NOT NULL ,
> [ObjectDependencies] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[ScheduleHistory] (
> [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [ScheduleID] [int] NOT NULL ,
> [StartDate] [datetime] NOT NULL ,
> [EndDate] [datetime] NULL ,
> [Succeeded] [bit] NULL ,
> [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NextStart] [datetime] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')


Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #3 (permalink)  
Old 02-29-2008, 02:39 AM
Dan Caron
 
Posts: n/a
Default Re: Purge Table Stored Procedure
That is exactly what I needed. Thank you very much Dan.

-Dan


"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message news:<MLvic.10091$e4.6777@newsread2.news.pas.earth link.net>...
> Assuming you want the keep the most recent rows based on StartDate, one
> method:
>
> DELETE FROM ScheduleHistory
> FROM Schedule s
> WHERE
> (
> SELECT COUNT(*)
> FROM ScheduleHistory sh
> WHERE
> sh.ScheduleID = ScheduleHistory.ScheduleID AND
> sh.ScheduleID = s.ScheduleID AND
> sh.StartDate >= ScheduleHistory.StartDate
> ) > s.RetainHistoryNum
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Dan Caron" <dancaron-listserv0687@mailblocks.com> wrote in message
> news:4d6e6a6c.0404231047.60cd40d0@posting.google.c om...
> > I have to create a stored procedure to purge "x" # of records from a
> > table. I have two tables (script below):
> >
> > Schedule
> > ScheduleHistory
> >
> > I need to purge records out of ScheduleHistory. The problem is that
> > the # of records that needs to be "kept" is dynamic, and stored in the
> > Schedule table. So all records in ScheduleHistory should be purged,
> > except for the most recent "x" number of records.
> >
> > For instance, for each Schedule row, you can specify how many records
> > to "keep" at all times. I want to keep the most recent "x" # of
> > ScheduleHistory rows for each Schedule.
> >
> > In the script below, I have two schedules ("test1" and "test2"). One
> > of them specifies that the most recent "5" records in ScheduleHistory
> > should be kept, and the other specifies that the most recent "3"
> > records in ScheduleHistory should be kept.
> >
> > So, I need a stored procedure that can clean up this table on demand.
> > I can't figure this one out...it's over my head.
> >
> > Dan
> >
> > -----------------
> > SCRIPT BELOW
> > -----------------
> >
> >
> > CREATE TABLE [dbo].[Schedule] (
> > [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> > [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NOT NULL ,
> > [TimeLapse] [int] NOT NULL ,
> > [TimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetryTimeLapse] [int] NOT NULL ,
> > [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetainHistoryNum] [int] NOT NULL ,
> > [ObjectDependencies] [varchar] (300) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE TABLE [dbo].[ScheduleHistory] (
> > [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> > [ScheduleID] [int] NOT NULL ,
> > [StartDate] [datetime] NOT NULL ,
> > [EndDate] [datetime] NULL ,
> > [Succeeded] [bit] NULL ,
> > [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [NextStart] [datetime] NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> >
> > INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> > INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> > INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply

« How to identify fragmented index for rebuild | Assigning parameters in a function »

Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On
Forum Jump


All times are GMT. The time now is 10:36 AM.

Contact Us - Unix Technical Forums - Top

Powered by vBulletin® Version 3.6.5
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
SEO by vBSEO 3.2.0
www.UnixAdminTalk.com