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

Jignesh KumarPosted Dec 7, 2022, 8:32 AM
Hello,
Please refer this thread which will help you,
https://social.technet.microsoft.com/wiki/contents/articles/37292.sql-server-2016-bulk-import-json-file-data-to-table.aspx
Vishal YelvePosted Dec 7, 2022, 8:29 AM
https://www.c-sharpcorner.com/article/sql-server-2016-bulk-import-json-file-data-to-table/
https://www.geeksforgeeks.org/how-to-import-json-data-into-sql-server/