SQL WAITFOR Command with WHILE Loop
I recently needed a way to execute a script overnight to count the number of records that were being updated in a given hour. I needed it to run at a specific time and to execute in 10 minute incriments and stop . I was hoping to log the results to a file, but this does just as well. When it’s done executing, it spits it all to the output window so I can see it in the morning when I get back.
This script below will start at 11pm and end at midnight and it will execute every 10 minutes and display the current time and the count. You could just as easily use a counter and have it execute a certain number of times.
–don’t even start until 11pm
WAITFOR TIME ’23:00:00.000′
declare @endtime datetime
set @endtime = cast(’2009-05-09 00:00:00.000′ as datetime)
–stop at midnight
while getdate() < @endtime
begin
–every 10 minutes, execute
WAITFOR DELAY ’00:10:00′;
select getdate(), count(*)
from tablename
where datetimecolumn > ’5/7/2009′
and datetimecolumn < ’5/8/2009′
end
Kudos to Jerry for helping me with this.
Recent Buzz