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.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 cleaning up records

Author  Topic 

dcobb
Yak Posting Veteran

76 Posts

Posted - 2002-06-05 : 08:35:18
Hi,

Is there any way of getting SQL Server to automatically delete old records from a table within pre-defined time periods?

Reason: I would like to extend my online shopping basket to automatically remove items which may be over 10 days old.

Any ideas?

Cheers

Dave

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-05 : 08:38:53
Use the following to delete:

DELETE FROM ShoppingCartTable WHERE DateDiff(dd, OrderDateColumn, getdate())>10

Create a new job in SQL Server, add the above statement as a T-SQL job step, then set the schedule for this job to run every day.

Go to Top of Page

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-06-05 : 08:44:37
Provided there is an index on your 'order date' column, I would modify Rob's command as shown below, so that the index gets used:

DELETE ShoppingCartTable
WHERE OrderDateColumn < DATEADD(dd, -10, CURRENT_TIMESTAMP)

You can see a related article of mine at: http://www.sqlteam.com/redir.asp?ItemID=9164

--
HTH,
Vyas
http://vyaskn.tripod.com
Go to Top of Page
   

- Advertisement -