Search code examples
c#jsont-sqlcross-apply

How to insert multiple JSON rows into a T-SQL table

I am trying to insert following JSON document into T-SQL:

  {
    "GLDETAIL": {
      "RECORDNO": "264378-1756289-919567--accrual",
      "BATCH_DATE": "02/01/2022"
    }
  },
  {
    "GLDETAIL": {
      "RECORDNO": "264378-1756290-919568--accrual",
      "BATCH_DATE": "02/01/2022"
    }
  }
,
  {
    "GLDETAIL": {
      "RECORDNO": "264379-1756291-919569--accrual",
      "BATCH_DATE": "02/01/2022"
    }
  },
  {
    "GLDETAIL": {
      "RECORDNO": "264379-1756292-919570--accrual",
      "BATCH_DATE": "02/01/2022"
    }
  }
,

This is T-SQL stored procedure that I was trying to fix, but only it inserted one row instead of four.

CREATE PROCEDURE [dbo].[InsertPerfCounterData1]

@json NVARCHAR(max)

AS
BEGIN

INSERT INTO dbo.PerfCounter2 (
  [RECORDNO]
 ,[BATCH_DATE]

)
SELECT
    RECORDNO,
    BATCH_DATE
    
FROM OPENJSON(@json) 
CROSS APPLY OPENJSON (@json)

WITH (

    RECORDNO     VARCHAR(MAX) '$.GLDETAIL.RECORDNO',
    BATCH_DATE   DATETIME2(7) '$.GLDETAIL.BATCH_DATE'
) AS jsonValues

END

This is result inside table.

RECORDNO                        BATCH_DATE
264378-1756289-919567--accrual  2022-02-01 00:00:00.0000000

I believe it is something to do with where it has "CROSS APPLY and OPENJSON", but I am not sure how to fix it.

Update:

I got this error after I applied the new SQL script that Larnu provided below:

enter image description here

System.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=JSON text is not properly formatted. Unexpected character ']' 
 is found at position 501.
  Source=Core .Net SqlClient Data Provider

This is C# code:

     using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sprocname, conn))
                {
                    // Set command object as a stored procedure
                    cmd.CommandType = CommandType.StoredProcedure;

                    // Add parameter that will be passed to stored procedure
                    cmd.Parameters.Add(new SqlParameter(paramName, paramValue));

                cmd.ExecuteReader();
            }
        }

enter image description here

Solution

  • This is because your JSON has multiple root nodes, and so SQL Server is only picking up the first. We can see this with the following:

    DECLARE @JSON nvarchar(MAX) = N'{
    "GLDETAIL": {
      "RECORDNO": "264378-1756289-919567--accrual",
      "BATCH_DATE": "02/01/2022"
    }
  },
  {
    "GLDETAIL": {
      "RECORDNO": "264378-1756290-919568--accrual",
      "BATCH_DATE": "02/01/2022"
    }
  }';

SELECT *
FROM OPENJSON(@JSON)

    Notice only one set is picked up, for GLDETAIL, not 2.

    We can "fix" this by making the data inside an array:

    DECLARE @JSON nvarchar(MAX) = N'{
    "GLDETAIL": {
      "RECORDNO": "264378-1756289-919567--accrual",
      "BATCH_DATE": "02/01/2022"
    }
  },
  {
    "GLDETAIL": {
      "RECORDNO": "264378-1756290-919568--accrual",
      "BATCH_DATE": "02/01/2022"
    }
  }
,
  {
    "GLDETAIL": {
      "RECORDNO": "264379-1756291-919569--accrual",
      "BATCH_DATE": "02/01/2022"
    }
  },
  {
    "GLDETAIL": {
      "RECORDNO": "264379-1756292-919570--accrual",
      "BATCH_DATE": "02/01/2022"
    }
  }';

SELECT GLD.RECORDNO,
       GLD.BATCH_DATE
FROM OPENJSON(CONCAT('[',@JSON,']')) OJ
     CROSS APPLY OPENJSON(OJ.[value],'$.GLDETAIL')
                  WITH (RECORDNO varchar(30),--USe an appropriate length, not MAX
                        BATCH_DATE date) GLD;