如何在TSQL中拆分API返回的CSV字符串并写入MSSQL数据表

前置依赖检查 #

你用到的 sp_OACreate 相关存储过程需要先开启 Ole Automation Procedures，已开启可跳过以下执行语句：

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

完整可运行代码 #

核心用XML实现多字符分隔符拆分，兼容SQL Server 2012及以上所有版本，无需额外依赖：

DECLARE @fromTime NVARCHAR(MAX)
DECLARE @toTime NVARCHAR(MAX)
DECLARE @URL2 NVARCHAR(MAX)

-- 提前建好目标表，建表参考：
-- CREATE TABLE Eirgrid_Co2Data (
--     [DATE & TIME] DATETIME,
--     [CO2 INTENSITY (gCO2/kWh)] INT,
--     [REGION] NVARCHAR(50)
-- )

SET @fromTime = REPLACE(FORMAT(DATEADD(DAY, -1, GETDATE()), 'dd-MMM-yyyy 00:00' ), ' ','%20')
SET @toTime = REPLACE(FORMAT(DATEADD(DAY, -1,GETDATE()), 'dd-MMM-yyyy 23:59' ), ' ','%20')

SELECT @URL2 = 
CONCAT('http://smartgriddashboard.eirgrid.com/DashboardService.svc/csv?area=co2Intensity&region=ALL&datefrom=',@fromTime,'&dateto=',@toTime)

DECLARE @URL NVARCHAR(MAX) = @URL2
DECLARE @Object as Int;
-- 原VARCHAR(8000)容量不足，替换为MAX类型兼容全天数据
DECLARE @ResponseText as NVARCHAR(MAX);
DECLARE @currenttime as datetime;
SET @currenttime = GETDATE()

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'get',
   @URL,
   'False'
EXEC sp_OAMethod @Object, 'send'
EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

IF((SELECT @ResponseText) <> '')
BEGIN
    DECLARE @xml XML
    -- 替换双空格行分隔符为XML节点标记，构造可解析XML结构
    SET @xml = CAST('<rows><row>' + REPLACE(@ResponseText, '  ', '</row><row>') + '</row></rows>' AS XML)

    ;WITH split_rows AS (
        SELECT 
            LTRIM(RTRIM(row.value('.', 'NVARCHAR(MAX)'))) AS row_content
        FROM @xml.nodes('/rows/row') AS T(row)
        WHERE LEN(LTRIM(RTRIM(row.value('.', 'NVARCHAR(MAX)')))) > 0
        AND row.value('position()[1]', 'INT') > 1 -- 跳过第一行表头
    ),
    split_cols AS (
        SELECT
            -- 按逗号拆分三列，去首尾空格
            LTRIM(RTRIM(PARSENAME(REPLACE(row_content, ',', '.'), 3))) AS data_time,
            LTRIM(RTRIM(PARSENAME(REPLACE(row_content, ',', '.'), 2))) AS co2_intensity,
            LTRIM(RTRIM(PARSENAME(REPLACE(row_content, ',', '.'), 1))) AS region
        FROM split_rows
    )
    -- 测试可先替换INSERT为SELECT查看结果是否正确
    INSERT INTO Eirgrid_Co2Data ([DATE & TIME], [CO2 INTENSITY (gCO2/kWh)], [REGION])
    SELECT 
        TRY_CAST(data_time AS DATETIME),
        TRY_CAST(co2_intensity AS INT),
        region
    FROM split_cols
     
END
ELSE
BEGIN
 DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';
 PRINT @ErroMsg;
END
EXEC sp_OADestroy @Object

注意事项 #

• 若后续字段扩展超过4列，把PARSENAME拆列逻辑替换为XML拆列即可，无需修改整体结构
• TRY_CAST会自动跳过格式异常的行，避免单次拉取全量失败
• 部署到SQL Agent Job时，确保运行账号有Ole Automation Procedures执行权限和目标表写入权限

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