SQL Query

7 Jun 2006 10:54 PM

murtaza

i've a database where huge data is dumped realtime, i'd like to run a query
in DTS which should keep only the last 90 days data and every day delete the
91st day.

hope i'm able to explain well. please advise necessary procedure ASAP.

8 Jun 2006 12:15 AM

Rob Farley

No need to do it in DTS - just have a SQL Job that runs daily, executing
something like:

delete from murtazastable where datecolumn < dateadd(day,-90,getdate())

But you could look into partitioning options, and have the data get archived
off. That way, you get to keep your data, but your main database (for backup
purposes) is kept smaller.

Show quote

"murtaza" <murtaz***@lycos.com> wrote in message
news:78E2D1F3-88A1-4811-813C-026BC4B67385@microsoft.com...
> i've a database where huge data is dumped realtime, i'd like to run a
> query
> in DTS which should keep only the last 90 days data and every day delete
> the
> 91st day.
>
> hope i'm able to explain well. please advise necessary procedure ASAP.

8 Jun 2006 8:17 AM

Steve Lloyd

Another option if the data is being updated often is to use a trigger that
either arhives or removes the old data.

Show quote

"murtaza" <murtaz***@lycos.com> wrote in message
news:78E2D1F3-88A1-4811-813C-026BC4B67385@microsoft.com...
> i've a database where huge data is dumped realtime, i'd like to run a
> query
> in DTS which should keep only the last 90 days data and every day delete
> the
> 91st day.
>
> hope i'm able to explain well. please advise necessary procedure ASAP.