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.
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