Deleting Data Using Job Scheduler
-
Hi all, I would like to delete a record in a SQL database after some time had passed if a certain field in that record has a value of No. For instance I would like to delete a record if the value of the Date field in that record is 5 days passed the timestamp date and the Returns Email field remains No. How do I do that using the Job Scheduler in SQL? I just thought that if I can use the Job Scheduler instead of writing a stored procedure, it would save time. Thanks in advance.
-
Hi all, I would like to delete a record in a SQL database after some time had passed if a certain field in that record has a value of No. For instance I would like to delete a record if the value of the Date field in that record is 5 days passed the timestamp date and the Returns Email field remains No. How do I do that using the Job Scheduler in SQL? I just thought that if I can use the Job Scheduler instead of writing a stored procedure, it would save time. Thanks in advance.
The script below should help. Just modify what is required. Job step as you already know will be T-SQL
DECLARE @DaysPassed INT -- Number of days required for record deletion
SELECT @DaysPassed = DATEDIFF(DD,GETDATE() - 5, GETDATE()) -- Diff in days eg (GETDATE() - 5, GETDATE() = today - (today - 5)
IF @DaysPassed = 5 -- If days paseed = 5 then...
BEGIN
SELECT GETDATE() -- Your delete statement can go here.
END
ELSE
PRINT 'I love Code project' -- If not equal to 5 then....
GOCheers
-
Hi all, I would like to delete a record in a SQL database after some time had passed if a certain field in that record has a value of No. For instance I would like to delete a record if the value of the Date field in that record is 5 days passed the timestamp date and the Returns Email field remains No. How do I do that using the Job Scheduler in SQL? I just thought that if I can use the Job Scheduler instead of writing a stored procedure, it would save time. Thanks in advance.
DELETE
[TableName]
WHERE
[TimeColName] < DATEADD(dd,-5,GETDATE()) AND
[EmailColName] = 'No'