如何从REST API URL直接将JSON数据导入SQL Server

SQL Server 原生没有内置OPENURL这类直接读取网络URL内容的函数，你理想中的简化写法无法直接生效，可通过以下几种方案达到相同的直接拉取API JSON、替代本地文件导入的效果：

方案1：使用OLE Automation调用WinHttp组件（无需额外安装工具） #

该方案通过系统自带的OLE Automation组件调用HTTP请求接口，直接获取API返回的JSON内容：

1. 先开启OLE Automation配置（仅需要执行一次，生产环境使用后可关闭降低安全风险）

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

2. 执行HTTP请求拉取JSON

DECLARE @url NVARCHAR(2000) = 'https://restapidata.com/mydata';
DECLARE @responseJson NVARCHAR(MAX);
DECLARE @winHttpObj INT;
DECLARE @httpStatus INT;

-- 初始化WinHttp请求对象
EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @winHttpObj OUT;
-- 配置GET请求
EXEC sp_OAMethod @winHttpObj, 'Open', NULL, 'GET', @url, 'false';
-- 发送请求
EXEC sp_OAMethod @winHttpObj, 'Send';
-- 获取请求状态码
EXEC sp_OAGetProperty @winHttpObj, 'Status', @httpStatus OUT;

IF @httpStatus = 200
BEGIN
    -- 读取接口返回的JSON内容，等价于你原来OPENROWSET返回的BulkColumn
    EXEC sp_OAGetProperty @winHttpObj, 'ResponseText', @responseJson OUT;
    SELECT @responseJson AS BulkColumn;
    -- 你也可以直接在此处用OPENJSON解析内容写入目标表，无需落地本地文件
    -- SELECT * FROM OPENJSON(@responseJson) WITH (字段名 字段类型 '$.json路径')
END
ELSE
BEGIN
    PRINT '接口请求失败，HTTP状态码：' + CAST(@httpStatus AS VARCHAR(10));
END

-- 释放请求对象
EXEC sp_OADestroy @winHttpObj;

注意：该方案需要SQL Server的运行服务账号拥有访问目标API的网络权限，HTTPS接口需要服务器信任对应站点的SSL证书

方案2：使用xp_cmdshell调用curl工具（适合Windows Server 2019及以上系统） #

Windows Server 2019、Win10及以上系统默认内置curl工具，可通过xp_cmdshell调用curl拉取JSON：

1. 开启xp_cmdshell配置（生产环境使用后建议关闭）

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

2. 执行拉取逻辑

DECLARE @url NVARCHAR(2000) = 'https://restapidata.com/mydata';
DECLARE @curlCmd NVARCHAR(4000) = 'curl -s "' + @url + '"';
DECLARE @tempResult TABLE (ContentLine NVARCHAR(MAX));

-- 调用curl拉取内容存入临时表
INSERT INTO @tempResult(ContentLine)
EXEC xp_cmdshell @curlCmd;

-- 合并curl返回的多行内容为完整JSON
DECLARE @responseJson NVARCHAR(MAX) = (
    SELECT STRING_AGG(ContentLine, '') WITHIN GROUP (ORDER BY (SELECT 1))
    FROM @tempResult
    WHERE ContentLine IS NOT NULL
);

SELECT @responseJson AS BulkColumn;

方案3：Azure SQL专用方案 #

如果你使用的是Azure SQL数据库，可直接调用内置的sp_invoke_external_rest_endpoint存储过程拉取内容，无需开启额外高权限配置：

DECLARE @responseJson NVARCHAR(MAX);
EXEC sp_invoke_external_rest_endpoint
    @url = N'https://restapidata.com/mydata',
    @method = N'GET',
    @response = @responseJson OUT;

SELECT @responseJson AS BulkColumn;

内容的提问来源于stack exchange，提问作者Taariq Toffar