在SQL Server 2019中递归导入JSON文件

INSERT INTO YourTable (JsonData)
SELECT BulkColumn
FROM OPENROWSET (BULK 'C:\Path\To\Your\File.json', SINGLE_CLOB) as j

{
  "employees": [
    {
      "name": "John",
      "age": 30,
      "department": "IT"
    },
    {
      "name": "Jane",
      "age": 35,
      "department": "HR"
    }
  ]
}

INSERT INTO Employees (Name, Age, Department)
SELECT
    JSON_VALUE(JsonData, '$.employees[0].name'),
    JSON_VALUE(JsonData, '$.employees[0].age'),
    JSON_VALUE(JsonData, '$.employees[0].department')
FROM YourTable

{
  "employees": [
    {
      "name": "John",
      "age": 30,
      "department": {
        "name": "IT",
        "subDepartment": {
          "name": "Development",
          "subDepartment": null
        }
      }
    },
    {
      "name": "Jane",
      "age": 35,
      "department": {
        "name": "HR",
        "subDepartment": null
      }
    }
  ]
}

WITH RecursiveCTE AS (
    SELECT
        JSON_VALUE(JsonData, '$.employees[0].name') as EmployeeName,
        JSON_VALUE(JsonData, '$.employees[0].department.name') as DepartmentName,
        JSON_VALUE(JsonData, '$.employees[0].department.subDepartment.name') as SubDepartmentName
    FROM YourTable
    WHERE JSON_VALUE(JsonData, '$.employees[0].department.name') IS NOT NULL
    UNION ALL
    SELECT
        JSON_VALUE(JsonData, '$.employees[0].name'),
        JSON_VALUE(JsonData, '$.employees[0].department.subDepartment.name'),
        JSON_VALUE(JsonData, '$.employees[0].department.subDepartment.subDepartment.name')
    FROM YourTable
    WHERE JSON_VALUE(JsonData, '$.employees[0].department.subDepartment.name') IS NOT NULL
)
INSERT INTO Departments (EmployeeName, DepartmentName, SubDepartmentName)
SELECT EmployeeName, DepartmentName, SubDepartmentName
FROM RecursiveCTE