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.
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?CheersDave |
|
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())>10Create 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. |
|
|
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,Vyashttp://vyaskn.tripod.com |
|
|
|
|
|