Paul Rajs

Paul Rajs

  • NA
  • 641
  • 146.9k

how to convert json data into table format and bulk insert in sql serv

Dec 7 2022 7:29 AM

Hi Developers,

how to convert json data into table format and bulk insert in sql server , 

Json Value : [
    {
      "groupUserAccessRight": "Contributor",
      "displayName": "EDGe_pbiapi",
      "identifier": "ee0454e8-bac6-494c-b324-22abe024afb8",
      "principalType": "Group"
    },
    {
      "groupUserAccessRight": "Contributor",
      "displayName": "Denodo – TD2SNF",
      "identifier": "b6929b22-f91e-47c2-a37f-d9fc3275b367",
      "principalType": "Group"
    }
  ]

I have to insert this value by bulk. 

What i am tried :


CREATE PROC [dbo].[SP_TEST]
@jsonValue NVARCHAR(MAX),
@workspaceid NVARCHAR(MAX),
@reportId NVARCHAR(MAX),
@UserId NVARCHAR(MAX)
AS
BEGIN TRY

BEGIN
DECLARE @groupUserAccessRight AS VARCHAR(max)
DECLARE @principalType AS VARCHAR(max)
DECLARE @EmailAddress AS VARCHAR(max)

INSERT INTO TEST (groupUserAccessRight,principalType) values(groupUserAccessRight,principalType)
SELECT groupUserAccessRight,principalType FROM  OPENJSON (@jsonValue)
WITH (
              groupUserAccessRight varchar(max) '$.groupUserAccessRight',
              principalType varchar(max) '$.principalType'
 ) 
 END

I did not get exact output. So if anyone know kindly suggest me to done this task. 

Regards,

Paul.S

 


Answers (2)