First published on MSDN on Mar 25, 2016
OPENJSON function enables you to easily write simple statement that loads array of JSON objects in table. Example is:
INSERT INTO dbo.People(Name, Surname)
SELECT Name, Surname
FROM OPENJSON (@json) WITH (Name nvarchar(100), Surname nvarchar(100))
See details in this post
OPENJSON – The easiest way to import JSON text into table
.
Although this is a simple command, it might be hard to write it if you have wide tables with 20-30 column. Also if some of the columns have special characters, you will need to surround them with [ ] in SQL names, and with " " in JSON paths.
Therefore, I have create a function that generates this script - you can download it here. This function looks like this:
CREATE FUNCTION
dbo.GenerateJsonInsertProcedure(@SchemaName sysname, @TableName sysname, @JsonColumns nvarchar(max))
RETURNS NVARCHAR(MAX)
In order to generate Insert stored procedure, you can specify Schema name of your table and table name. Also if you have some columns in table that contain JSON text and if you will have some nested JSON in your input, you can specify list of these columns in @JsonColumns parameter.
Now, let's see hot it works. I will generate JSON insert stored procedure for Person.Address table:
declare @SchemaName sysname = 'Person' --> Name of the table where we want to insert JSON
declare @TableName sysname = 'Address' --> Name of the table schema where we want to insert JSON
declare @JsonColumns nvarchar(max) = '||' --> List of pipe-separated NVARCHAR(MAX) column names that contain JSON text, e.g. '|AdditionalInfo|Demographics|'
print (dbo.GenerateJsonInsertProcedure(@SchemaName, @TableName, @JsonColumns))
In this case I will just print script that function returns. Output will be:
DROP PROCEDURE IF EXISTS [Person].[AddressInsertJson]
GO
CREATE PROCEDURE [Person].[AddressInsertJson](@Address NVARCHAR(MAX))
AS BEGIN
INSERT INTO Address([AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[ModifiedDate])
SELECT [AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[ModifiedDate]
FROM OPENJSON(@AddressJson)
WITH (
[AddressLine1] nvarchar(120) N'strict $."AddressLine1"',
[AddressLine2] nvarchar(120) N'$."AddressLine2"',
[City] nvarchar(60) N'strict $."City"',
[StateProvinceID] int N'strict $."StateProvinceID"',
[PostalCode] nvarchar(30) N'strict $."PostalCode"',
[ModifiedDate] datetime N'strict $."ModifiedDate"')
END
Function will go through all columns in the specified table, check what is the type, is it required column (in that case it will generate $.strict modifier in path) and create script. You can modify this query and remove unnecessary columns if you want.
Script that generates insert procedure is
here
.
