software-mariodiana
11/23/2014 - 12:21 AM

Creating an SQL database API cursor to fetch the rows from a database table a thousand rows at a time, leaving it to the database to figure

Creating an SQL database API cursor to fetch the rows from a database table a thousand rows at a time, leaving it to the database to figure out how to keep track of the data. This example is written for Microsoft's SQL Server. You would use something like this if you had a database table that lacked any easily sorted fields. In other words, let the database sort the data any way it wants. What is important is not the sorting, but rather not getting the data all at once. The (trivial) query here is "SELECT MyColumn FROM MyTable". Replace that with whatever query you have.

SqlAPICursor.sql
content_copyfile_download
-- http://dba.stackexchange.com/a/82806
DECLARE @cur INTEGER
    ,
    -- FAST_FORWARD | AUTO_FETCH | AUTO_CLOSE
    @scrollopt INTEGER = 16 | 8192 | 16384
    ,
    -- READ_ONLY, CHECK_ACCEPTED_OPTS, READ_ONLY_ACCEPTABLE
    @ccopt INTEGER = 1 | 32768 | 65536
    ,@rowcount INTEGER = 1000
    ,@rc INTEGER;

-- Open the cursor and return the first 1,000 rows
EXECUTE @rc = sys.sp_cursoropen @cur OUTPUT
    ,'SELECT MyColumn FROM MyTable'
    ,@scrollopt OUTPUT
    ,@ccopt OUTPUT
    ,@rowcount OUTPUT;

IF @rc <> 16 -- FastForward cursor automatically closed
BEGIN
    -- Name the cursor so we can use CURSOR_STATUS
    EXECUTE sys.sp_cursoroption @cur
        ,2
        ,'MyCursorName';

    -- Until the cursor auto-closes
    WHILE CURSOR_STATUS('global', 'MyCursorName') = 1
    BEGIN
        EXECUTE sys.sp_cursorfetch @cur
            ,2
            ,0
            ,1000;
    END;
END;

-- Refer to:
-- 1. sp_cursoropen   - http://msdn.microsoft.com/en-us/library/ff848737.aspx 
-- 2. sp_cursoroption - http://msdn.microsoft.com/en-us/library/ff848779.aspx
-- 3. sp_cursorfetch  - http://msdn.microsoft.com/en-us/library/ff848736.aspx