Quick Tip: How to Take Snapshots of Data at Specified Intervals
A few weeks ago I was debugging something and needed to take snapshots of a table every five seconds, as a quick solution a colleague started running the following SQL and I thought I'd share it for anyone else stuck in a pinch...
WHILE (1=1) BEGIN INSERT INTO tmp_snapshot SELECT field1,field2,field3 FROM table1 WHERE field4 = 'value' WAITFOR DELAY '000:00:05' END
Just change the time delay and the query and you're off, of course you could be a lot cleverer with your WHILE conditions and rework this principle for loads of things. I thought it worth posting as many database folk forget the power of the humble while loop!