能否将两个字段不同的JSON文件加载到同一个SQL表中？

目前我通过两套不同代码将两个字段不同的JSON文件加载到两个不同的临时表中，之后再进行合并。我希望直接将它们都加载到同一个临时表中：对于File A，affiliations和language字段设为NULL；对于File B，specialties和age字段设为NULL。请问这是否可行？还是说文件中的字段必须与表中的字段完全匹配？根据我的经验，若表中存在文件没有的列，加载会失败。

附原加载代码：

File A 原代码 #

Declare @CustomerA varchar (max)
SELECT @CustomerA=BULKCOLUMN
FROM OPENROWSET (BULK '\\hlmofile.net\shared\IS\Automation\Cust\Data\', SINGLE_CLOB) json 
insert into [Customer].[CustomerA]
  SELECT 
    JSON_VALUE(a.value, '$.name') as name,
    JSON_VALUE(a.value, '$.address') as address,
    JSON_VALUE(a.value, '$.city') as city,
    JSON_VALUE(a.value, '$.state') as tate, -- 注意：此处笔误将state写成了tate
    JSON_QUERY(a.value, '$.specialties') as specialties, 
    JSON_VALUE(a.value, '$.age') as age
  FROM OPENJSON(@CustomerA ) as a

File B 原代码 #

Declare @CustomerB varchar (max)
SELECT @CustomerB=BULKCOLUMN
FROM OPENROWSET (BULK '\\hlmofile.net\shared\IS\Automation\Cust\Data\', SINGLE_CLOB) json 
insert into [Customer].[CustomerB]
  SELECT 
    JSON_VALUE(a.value, '$.name') as name,
    JSON_VALUE(a.value, '$.address') as address,
    JSON_VALUE(a.value, '$.city') as city,
    JSON_VALUE(a.value, '$.state') as tate, -- 同样存在state笔误为tate的问题
    JSON_QUERY(a.value, '$.affiliations') as affiliations, 
    JSON_VALUE(a.value, '$.language') as language
  FROM OPENJSON(@CustomerB ) as a

这完全可行，不需要文件字段与表字段完全匹配。只要在INSERT语句中显式指定目标表的列，并为JSON文件中不存在的字段提供NULL值即可，不会出现加载失败的情况。

步骤1：创建统一的目标表（如果未存在） #

先确保目标表包含所有需要的字段：

CREATE TABLE [Customer].[CustomerCombined] (
    name VARCHAR(255),
    address VARCHAR(255),
    city VARCHAR(100),
    [state] VARCHAR(50), -- 修正原代码中的笔误字段名
    specialties NVARCHAR(MAX), -- 用NVARCHAR存储JSON_QUERY结果更适配
    age INT,
    affiliations NVARCHAR(MAX),
    language VARCHAR(100)
)

步骤2：修改加载代码，统一插入到目标表 #

加载File A到统一表

DECLARE @CustomerA VARCHAR(MAX)
SELECT @CustomerA = BULKCOLUMN
FROM OPENROWSET (BULK '\\hlmofile.net\shared\IS\Automation\Cust\Data\FileA.json', SINGLE_CLOB) json -- 建议明确指定文件名，避免加载目录下所有文件
INSERT INTO [Customer].[CustomerCombined] (name, address, city, [state], specialties, age, affiliations, language)
SELECT 
    JSON_VALUE(a.value, '$.name') AS name,
    JSON_VALUE(a.value, '$.address') AS address,
    JSON_VALUE(a.value, '$.city') AS city,
    JSON_VALUE(a.value, '$.state') AS [state], -- 修正笔误
    JSON_QUERY(a.value, '$.specialties') AS specialties, 
    JSON_VALUE(a.value, '$.age') AS age,
    NULL AS affiliations, -- File A无此字段，设为NULL
    NULL AS language -- File A无此字段，设为NULL
FROM OPENJSON(@CustomerA) AS a

加载File B到统一表

DECLARE @CustomerB VARCHAR(MAX)
SELECT @CustomerB = BULKCOLUMN
FROM OPENROWSET (BULK '\\hlmofile.net\shared\IS\Automation\Cust\Data\FileB.json', SINGLE_CLOB) json -- 明确指定文件名
INSERT INTO [Customer].[CustomerCombined] (name, address, city, [state], specialties, age, affiliations, language)
SELECT 
    JSON_VALUE(a.value, '$.name') AS name,
    JSON_VALUE(a.value, '$.address') AS address,
    JSON_VALUE(a.value, '$.city') AS city,
    JSON_VALUE(a.value, '$.state') AS [state], -- 修正笔误
    NULL AS specialties, -- File B无此字段，设为NULL
    NULL AS age, -- File B无此字段，设为NULL
    JSON_QUERY(a.value, '$.affiliations') AS affiliations, 
    JSON_VALUE(a.value, '$.language') AS language
FROM OPENJSON(@CustomerB) AS a

关键说明 #

• 显式指定INSERT的目标列是核心：SQL Server能明确对应每一列的取值，即使JSON中没有对应字段，只要提供合法默认值（此处为NULL）就能正常插入。
• 修正了原代码中state被误写为tate的笔误，避免字段名不匹配问题。
• 建议在OPENROWSET的BULK路径中明确指定具体文件名，防止加载目录下所有文件导致数据混乱。

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