INSERT INTO for JSON array-CodePudding

I'm getting text files where some columns are populated with a JSON array. I import the file into a staging table. I want to use INSERT INTO to get the file into another table with the JSON array column parsed into two columns.

I need one column named 'military_focus' and another named 'health_insurance_focus' that will be populated with 'true' or 'false'. Using this SELECT statement presents the data as I need it.

    SELECT
[network_id]
[network_name],
[network_type],
[service_type_ids],
JSON_VALUE(focus,'$.military_focus') AS military_focus,
JSON_VALUE(focus,'$.health_insurance_focus') AS health_insurance_focus,
[created_at],
[updated_at],
[LoadDt],
[FileNM]
FROM
[Med_Stage].[Provider].[networks]

I'm trying to use that with an INSERT INTO to get it into another table with the appropriate columns. I get an error that the SELECT values do not match the number of INSERT columns since I'm going from one 'focus' column in the Staging table to two columns in the destination table.

      INSERT INTO  [Med].[Provider].[networks]
  (
    [network_id],
    [network_name],
    [network_type],
    [service_type_ids],
    [military_focus],
    [health_insurance_focus],
    [created_at],
    [updated_at],
    [LoadDt],
    [FileNM]
      )

    SELECT
    [network_id]
    [network_name],
    [network_type],
    [service_type_ids],
    JSON_VALUE(focus,'$.military_focus') AS military_focus,
    JSON_VALUE(focus,'$.health_insurance_focus') AS health_insurance_focus,
    [created_at],
    [updated_at],
    [LoadDt],
    [FileNM]
    FROM
    [Med_Stage].[Provider].[networks]

CodePudding user response：

Yes, the fiddle is sufficent, being able to test immediately highlights the issue.

You're just missing a comma after the first column

SELECT
[network_id], /* <-- missing comma*/
[network_name],
[network_type],
[service_type_ids],
JSON_VALUE(focus,'$.military_focus') AS military_focus,
JSON_VALUE(focus,'$.health_insurance_focus') AS health_insurance_focus,
[created_at],
[updated_at],
[LoadDt],
[FileNM]
FROM
[Med_Stage].[Provider].[networks]