SQL While Loop 60 seconds?

Question

I'm basically trying to caputure sql commands and their wait time for CPU, and I simply want to run a while loop for 60 seconds and insert all the data into a temp table. I'm not very good while cursors, or how to accomplish this one in sql. ``` BEGIN CREATE TABLE #DiagTable ( sessionID NVARCHAR (MAX), dbname NVARCHAR (MAX), starttime NVARCHAR (MAX), cmd NVARCHAR (MAX), stat NVARCHAR (MAX), cputime NVARCHAR (MAX), totalelapsedtime NVARCHAR (MAX), reads NVARCHAR (MAX), writes NVARCHAR (MAX), [query] NVARCHAR (MAX) ) DECLARE @id INT DECLARE x CURSOR FOR SELECT 60 OPEN x FETCH NEXT FROM x INTO @id WHILE @@FETCH_STATUS = 0 BEGIN WAITFOR DELAY '000:00:01' -- begin loop here INSERT INTO #DiagTable SELECT a.session_id, db_name (a.database_id) AS db_name, a.start_time, a.command, a.status, a.cpu_time, a.total_elapsed_time, a.reads, a.writes, b.text AS query FROM sys.dm_exec_requests a OUTER APPLY sys.dm_exec_sql_text (a.sql_handle) b WHERE a.session_id > 50 -- filter out background tasks AND…

Accepted Answer

To execute you script every second for one minute you could so something like this

```
DECLARE @i INT = 1;

WHILE (@i <= 60)
 BEGIN
  WAITFOR DELAY '00:00:01'

/*Your Script*/

SET  @i = @i + 1;
END 
```

I would Add another column in the temp table #DiagTable `Session INT` and add Value of @i in that column just to keep track of records inserted by each `loop`.

SQL While Loop 60 seconds?

Solution

Problem