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:

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:

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.