Insert data into a table using OPENJson

Question

I have the following Json that needs to be inserted into a table

{"errorMessage":"","requestOutput":{   "REQUEST_ID" : 100910,   "STATUS_CODE" : "P",   "PHASE_CODE" : 
 "N",   "COMPLETION_TEXT" : null }}

This is the query i am using to process the json and insert into the table

INSERT INTO dbo.tblRequest_Details([RequestId]
      ,[Error_Message]
      ,[Status_Code]
      ,[Phase_Code]
      ,[Completion_Text])
    SELECT  [RequestId]
      ,[Error_Message]
      ,[Status_Code]
      ,[Phase_Code]
      ,[Completion_Text]
    FROM OPENJSON((select * from @json2), N'$.requestOutput')
    WITH (  
             [RequestId]   nvarchar(max) N'$.REQUEST_ID',
             [Status_Code]   nvarchar(max) N'$.STATUS_CODE',
             [Phase_Code]   nvarchar(max) N'$.PHASE_CODE',
             [Completion_Text]   nvarchar(max) N'$.COMPLETION_TEXT')            
             CROSS APPLY OPENJSON((select * from @json2), N'$.errorMessage')
              WITH( [Error_Message] nvarchar(max) N'$.errorMessage'
        
    )

But for some reason, data does not get inserted into the table. What am I doing wrong?

Thanks

score 1 · Answer 1 · answered Nov 12 '20 at 18:57

1

Turns out there was an error with the errorMessage field so I removed the cross apply. Instead assigned the value of errorMessage to a variable and used that instead.

answered Nov 12 '20 at 18:57

jaiya

53
4

Insert data into a table using OPENJson

1 Answers1