sql-serverstored-proceduressql-server-json

Insert rows into SQL Server table from json


Trying to work up a stored procedure to insert json as rows into an existing empty table. The json properties match the table columns.

This stored procedure returns 3 errors shown below the following code

declare @json nvarchar(max)
declare @db_name sysname = 'test1'
set @json= N'{
"Cutouts":
[      
    {
        "ItemCode":"Circle",
        "MinutesLabor":8         
    },
    {
        "ItemCode":"Star",
        "MinutesLabor":10
    }   
]
}';
--print @json
declare @str nvarchar(max)

set @str = N'INSERT [' + QUOTENAME (@db_name) + '].[dbo].[Cutouts] (ItemCode, MinutesLabor)
SELECT ItemCode, MinutesLabor
FROM OPENJSON(' + @json + ')
     WITH (
     ItemCode varchar(8) ''$.Cutouts.ItemCode'', 
     MinutesLabor decimal(9,1) ''$.Cutouts.MinutesLabor''';
--print @str
exec (@str)

The errors are

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'Cutouts'.

I've tried to follow the json format used in an example of working with json in sql server at SQLShack. I've validated the json at JSONLint

The identifier that starts with ' { "ItemCode":"Circle", "MinutesLabor":8 ' is too long. Maximum length is 128.

I've tried starting the sp with SET QUOTED_IDENTIFIER ON and SET QUOTED_IDENTIFIER OFF and the same error is thrown in each case

The third error is

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

The previous statement does end in a semi-colon

There are many examples showing how to insert json into SQL Server and I've tried to follow as best I can, but the scenarios all seem more complex than mine and yet I still can't get it right.


Solution

  • You have a few formatting issues.

    Firstly when you are concatenating your json with your Sql string, it needs to be quoted; You need a closing bracket at the end and you can define the json path in the OpenJson element. The following works:

    declare @json nvarchar(max)
    declare @db_name sysname = 'test1'
    set @json= N'''{
    "Cutouts":
    [      
        {
            "ItemCode":"Circle",
            "MinutesLabor":8         
        },
        {
            "ItemCode":"Star",
            "MinutesLabor":10
        }   
    ]
    }'',''$.Cutouts''';
    --print @json
    declare @str nvarchar(max)
    
    set @str = N'INSERT [' + QUOTENAME (@db_name) + '].[dbo].[Cutouts] (ItemCode, MinutesLabor)
    SELECT ItemCode, MinutesLabor
    FROM OPENJSON(' + @json + ')
         WITH (
         ItemCode varchar(8) ''$.ItemCode'', 
         MinutesLabor decimal(9,1) ''$.MinutesLabor'')';
    --print @str
    exec (@str)
    

    See DB Fiddle