By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table [table](year_ int, state_ varchar(50),
report varchar(50),farmtype varchar(50),category varchar(50),category_value varchar(50),category2 varchar(50),category2_value varchar(50),
variable_id varchar(50),variable_name varchar(50),variable_sequence int,variable_level int,
variable_group varchar(50),variable_group_id varchar(50),variable_unit varchar(50),
variable_description varchar(50),variable_is_invalid varchar(50),estimate int,median varchar(50),statistic varchar(50),
rse int,unreliable_estimate int,decimal_display int)
CREATE PROCEDURE load_proc
@json NVARCHAR(MAX),
@table_name VARCHAR(50)
AS
begin
declare @sql varchar(max)
set @sql= 'INSERT INTO ' + QUOTENAME(@table_name) + '(year_, state_,
report,farmtype,category,category_value,category2,category2_value,
variable_id,variable_name,variable_sequence,variable_level,
variable_group,variable_group_id,variable_unit,
variable_description,variable_is_invalid,estimate,median,statistic,
rse,unreliable_estimate,decimal_display) SELECT year_,
state_,
report,
farmtype,
category,
category_value,
category2,
category2_value,
variable_id,
variable_name,
variable_sequence,
variable_level,
variable_group,
variable_group_id,
variable_unit,
variable_description,
variable_is_invalid,
estimate,
median,
statistic,
rse,
unreliable_estimate,
decimal_display
FROM OPENJSON('''+ @json +''',' + '''$'')
WITH (
declare @json varchar(8000)
set @json = '[{
"year": 2021,
"state": "Nebraska",
"report": "Farm Business Balance Sheet",
"farmtype": "All Farms",
"category": "Operator Age",
"category_value": "45 to 54 years old",
"category2": "All Farms",
"category2_value": "TOTAL",
"variable_id": "kount",
"variable_name": "Farms",
"variable_sequence": 10,
"variable_level": 1,
"variable_group": null,
"variable_group_id": null,
"variable_unit": "Number",
"variable_description": "Estimated number of farms.",
"variable_is_invalid": false,
"estimate": 5889,
"median": null,
"statistic": "TOTAL",
"rse": 0,
"unreliable_estimate": 0,
"decimal_display": 0
},
{
"year": 2021,
"state": "Nebraska",
"report": "Farm Business Balance Sheet",
"farmtype": "All Farms",
"category": "Farm Typology",
"category_value": "Off-farm occupation farms (2011 to present)",
"category2": "All Farms",
"category2_value": "TOTAL",
"variable_id": "kount",
2 rows affected
SELECT *
FROM [table]
| year_ | state_ | report | farmtype | category | category_value | category2 | category2_value | variable_id | variable_name | variable_sequence | variable_level | variable_group | variable_group_id | variable_unit | variable_description | variable_is_invalid | estimate | median | statistic | rse | unreliable_estimate | decimal_display |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2021 | Nebraska | Farm Business Balance Sheet | All Farms | Operator Age | 45 to 54 years old | All Farms | TOTAL | kount | Farms | 10 | 1 | null | null | Number | Estimated number of farms. | false | 5889 | null | TOTAL | 0 | 0 | 0 |
| 2021 | Nebraska | Farm Business Balance Sheet | All Farms | Farm Typology | Off-farm occupation farms (2011 to present) | All Farms | TOTAL | kount | Farms | 10 | 1 | null | null | Number | Estimated number of farms. | false | 13398 | null | TOTAL | 0 | 0 | 0 |