title: SQL While Loop 60 seconds?
body: <p>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.  </p>
  
  <pre>
  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 a.session_id  @@spid -- filter out this query session
           ORDER BY a.cpu_time DESC;
  
        WAITFOR DELAY '00:00:01'
  
        FETCH NEXT FROM x   INTO @id
     END
  
     CLOSE x
  
     DEALLOCATE x
  
     -- end loop here
     SELECT * FROM #DiagTable;
  
     DROP TABLE #DiagTable;
  END
  </pre>
  
  <p>not sure if select 60 is what i want...</p>