SQL Server使用OPENJSON调用分页API拉取全量数据的实现方案

现有单页请求、JSON解析逻辑可直接复用，核心是通过WHILE循环逐页构造请求，从第一页响应的元数据中提取总记录数计算总页数，作为循环终止判断条件，每次解析完当前页数据直接存入结果表即可，完整实现如下：

完整实现代码 #

-- 建表存储全量拉取结果，正式环境可替换为业务物理表
CREATE TABLE #CatalogResult(
    id NVARCHAR(MAX),
    sku NVARCHAR(MAX),
    status NVARCHAR(MAX),
    highlight NVARCHAR(MAX),
    is_new NVARCHAR(MAX), -- new为SQL关键字，加别名避免语法报错
    stock NVARCHAR(MAX),
    price_table NVARCHAR(MAX)
)

-- 定义循环及请求相关变量
DECLARE @token INT;
DECLARE @ret INT;
DECLARE @url NVARCHAR(MAX);
DECLARE @json AS TABLE(Json_Table NVARCHAR(MAX));
DECLARE @currentPage INT = 1; -- 从第一页开始请求
DECLARE @pageSize INT = 10000; -- 单页拉取量和之前测试值保持一致
DECLARE @totalCount INT = 0;
DECLARE @totalPages INT = 0;
DECLARE @apiBase NVARCHAR(MAX) = 'https://api.xpto.io/v1/catalog?X-API-KEY=ABCD123456';

-- 分页循环拉取
WHILE @totalPages = 0 OR @currentPage <= @totalPages
BEGIN
    -- 清空上一页的临时JSON缓存
    DELETE FROM @json;
    -- 拼接当前页请求地址
    SET @url = @apiBase + '&limit=' + CAST(@pageSize AS NVARCHAR(10)) + '&page=' + CAST(@currentPage AS NVARCHAR(10));

    -- 初始化HTTP请求对象
    EXEC @ret = sp_OACreate 'MSXML2.XMLHTTP', @token OUT;
    IF @ret <> 0 RAISERROR('创建HTTP连接失败，当前页号：%d', 16, 1, @currentPage);

    -- 发送GET请求
    EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false';
    IF @ret <> 0 RAISERROR('初始化请求失败，当前页号：%d', 16, 1, @currentPage);
    EXEC @ret = sp_OAMethod @token, 'send';
    IF @ret <> 0 RAISERROR('发送请求失败，当前页号：%d', 16, 1, @currentPage);

    -- 读取响应JSON存入临时表
    INSERT INTO @json (Json_Table) EXEC sp_OAGetProperty @token, 'responseText';

    -- 第一页请求时提取总记录数，计算总页数
    IF @totalPages = 0
    BEGIN
        SELECT @totalCount = total
        FROM OPENJSON((SELECT * FROM @json))
        WITH (
            total INT '$.metadata.total' -- 按实际API返回的总数字段JSON路径调整
        )
        -- 向上取整计算总页数，避免最后一页不足单页量时被遗漏
        SET @totalPages = CEILING(@totalCount * 1.0 / @pageSize);
    END

    -- 解析当前页数据写入结果表
    INSERT INTO #CatalogResult(id, sku, status, highlight, is_new, stock, price_table)
    SELECT
        metadata.[id],
        metadata.[sku],
        metadata.[status],
        metadata.[highlight],
        metadata.[new],
        metadata.[stock],
        prices.[price_table]
    FROM OPENJSON((SELECT * FROM @json))
    WITH (
        [items] NVARCHAR(MAX) AS JSON
    ) AS Data
    CROSS APPLY OPENJSON(Data.[items])
    WITH(
        [id] NVARCHAR(MAX),
        [sku] NVARCHAR(MAX),
        [status] NVARCHAR(MAX),
        [highlight] NVARCHAR(MAX),
        [new] NVARCHAR(MAX),
        [stock] NVARCHAR(MAX),
        [prices] NVARCHAR(MAX) AS JSON
    ) AS metadata
    CROSS APPLY OPENJSON(metadata.[prices])
    WITH(
        [price_table] NVARCHAR(MAX)
    ) AS prices;

    -- 释放HTTP对象避免句柄泄漏
    EXEC sp_OADestroy @token;
    -- 页码自增进入下一轮
    SET @currentPage = @currentPage + 1;

    -- 若接口有请求频率限制，打开下一行设置1秒请求间隔，避免被限流
    -- WAITFOR DELAY '00:00:01';
END

-- 全量拉取完成后查询结果，或直接写入业务表
SELECT * FROM #CatalogResult;
-- 校验拉取总数是否和预期13494一致
SELECT COUNT(*) AS TotalFetched FROM #CatalogResult;

DROP TABLE #CatalogResult;

注意事项 #

• 运行前需要开启SQL Server的OLE Automation权限（需要sysadmin角色），执行以下配置脚本：

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

• 代码中读取总记录数的JSON路径$.metadata.total需要和实际API返回结构匹配，第一页请求后可打印@totalCount变量确认取值正确。
• 如果API单页最大支持的拉取条数小于10000，对应修改@pageSize参数即可，循环逻辑会自动重新计算总页数。
• 正式跑全量前可先把@pageSize设为10，测试2-3页循环逻辑正常后再改回最大值，减少调试成本。

内容的提问来源于stack exchange，提问作者Hugo Pereira