如何将复杂嵌套JSON导入SQL Server多表？解决列数不匹配报错

先排查「列数不匹配」的核心原因 #

出现这个错误的本质是你INSERT语句中SELECT返回的列数、字段顺序，和目标表的定义不完全匹配。先执行单独的SELECT语句，把结果和目标表的列清单对比，确认：

• 列数完全一致，没有多列或少列
• 字段顺序和目标表列顺序对应（SQL Server默认按顺序匹配，除非指定列名）
• 所有必填列都有返回值（允许NULL的列可以返回NULL，但不能缺失）

分表导入的具体实现代码 #

假设你的JSON结构大致为：

{
  "meta": { "disclaimer": "...", "terms": "...", "last_updated": "..." },
  "results": [
    {
      "id": "abc123",
      "drug_name": "...",
      "openfda": {
        "brand_name": ["..."],
        "generic_name": ["..."],
        "manufacturer_name": ["..."]
      }
    }
  ]
}

1. 导入Meta表 #

直接提取meta节点的字段，确保和Meta表列一一对应：

INSERT INTO Meta (disclaimer, terms, last_updated)
SELECT
  JSON_VALUE(jsonData, '$.meta.disclaimer') AS disclaimer,
  JSON_VALUE(jsonData, '$.meta.terms') AS terms,
  JSON_VALUE(jsonData, '$.meta.last_updated') AS last_updated
FROM OPENROWSET(BULK 'D:\fda_drugs.json', SINGLE_CLOB) AS j(jsonData)

2. 导入Results表 #

用CROSS APPLY OPENJSON拆分results数组，每个数组元素对应一行数据，同时提取唯一标识（比如JSON里的id）用于关联后续的ResultOpenfda表：

INSERT INTO Results (ResultID, drug_name)
SELECT
  JSON_VALUE(result.value, '$.id') AS ResultID,
  JSON_VALUE(result.value, '$.drug_name') AS drug_name
  -- 依次添加Results表的其他字段，确保列数和表一致
FROM OPENROWSET(BULK 'D:\fda_drugs.json', SINGLE_CLOB) AS j(jsonData)
CROSS APPLY OPENJSON(jsonData, '$.results') AS result

如果Results表用自增主键，SELECT里去掉ResultID，让数据库自动生成即可

3. 导入ResultOpenfda表 #

通过两次CROSS APPLY先拆results数组，再拆每个结果下的openfda节点，同时关联Results表的ResultID作为外键：

INSERT INTO ResultOpenfda (ResultID, brand_name, generic_name, manufacturer_name)
SELECT
  JSON_VALUE(result.value, '$.id') AS ResultID,
  -- openfda的字段多为数组，用[0]取第一个元素，若要导入所有数组元素需再拆一次OPENJSON
  JSON_VALUE(openfda.value, '$.brand_name[0]') AS brand_name,
  JSON_VALUE(openfda.value, '$.generic_name[0]') AS generic_name,
  JSON_VALUE(openfda.value, '$.manufacturer_name[0]') AS manufacturer_name
  -- 添加ResultOpenfda表的其他字段
FROM OPENROWSET(BULK 'D:\fda_drugs.json', SINGLE_CLOB) AS j(jsonData)
CROSS APPLY OPENJSON(jsonData, '$.results') AS result
CROSS APPLY OPENJSON(result.value, '$.openfda') AS openfda

处理复杂嵌套数组的补充方案 #

如果openfda下的字段是多元素数组（比如spl_id有多个值），需要把每个数组元素单独导入一行，可再嵌套一次OPENJSON：

INSERT INTO ResultOpenfda (ResultID, spl_id)
SELECT
  JSON_VALUE(result.value, '$.id') AS ResultID,
  spl_id.value AS spl_id
FROM OPENROWSET(BULK 'D:\fda_drugs.json', SINGLE_CLOB) AS j(jsonData)
CROSS APPLY OPENJSON(jsonData, '$.results') AS result
CROSS APPLY OPENJSON(result.value, '$.openfda.spl_id') AS spl_id

验证技巧 #

• 先执行SELECT * FROM OPENROWSET(BULK '路径', SINGLE_CLOB) AS j查看完整JSON结构
• 用SELECT * FROM OPENJSON(jsonData, '$.results')查看results数组的所有字段，避免遗漏
• 对不确定的JSON路径，用JSON_QUERY先提取节点内容，确认结构是否正确

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