September 2, 2008 at 9:42 pm
I want automation(self execution) of a delete query at a particular time everyday for deleting records of 40 day ago
This query shud be executed everyday automatically and if it finds record of today-40 days,then this record shud be deleted
September 2, 2008 at 9:47 pm
Easiest way I can think of is a Job. Expand the menu for SQL Server Agent on the left side. Jobs are one of the folders below that. Right click and create a new one. You will be able to give it specific SQL code and schedule repeating dates/times to run it.
Hope that helps.
September 3, 2008 at 1:49 am
Job is the best solution...
make a Sp for you delete command. Make sure that the sp do not accepts parameters and no output parameter is used.
Associate this sp with a job.
Atif Sheikh
September 3, 2008 at 2:04 am
If you are asking how to schedule the query then yes, a SQL Server Agent job will do the trick. If you are asking about the SQL, then check out the dateadd() and getdate() functions in books online. Combine them like this
...WHERE some_date_column < dateadd(day, -40, getdate())
or by using datediff()
...WHERE datediff(day, some_date_column , getdate()) > 40
don't forget to take a backup first 🙂
September 3, 2008 at 4:13 am
If you have an index on the datetime field, don't use functions in the WHERE clause
"...WHERE datediff(day, some_date_column , getdate()) > 40"
in this case, the index will be ignored.
It's a better solution, in terms of performance, the use of:
"...WHERE some_date_column < dateadd(day, -40, getdate())"
Perhaps not in this case, because you have a lot of information to delete, but it's better to think in solutions considering performance requeriments.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply