SQL Server 2022

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 (
            year_ varchar(255) ' + '''$.year'',
state_ varchar(255) ' + '''$.state'',
report varchar(255) ' + '''$.report'',
farmtype varchar(255) ' + '''$.farmtype'',
category varchar(255) ' + '''$.category'',
category_value varchar(255) ' + '''$.category_value'',
category2 varchar(255) ' + '''$.category2'',
category2_value varchar(255) ' + '''$.category2_value'',
variable_id varchar(255) ' + '''$.variable_id'',
variable_name varchar(255) ' + '''$.variable_name'',
variable_sequence varchar(255) ' + '''$.variable_sequence'',
variable_level varchar(255) ' + '''$.variable_level'',
variable_group varchar(255) ' + '''$.variable_group'',
variable_group_id varchar(255) ' + '''$.variable_group_id'',
variable_unit varchar(255) ' + '''$.variable_unit'',
variable_description varchar(255) ' + '''$.variable_description'',
variable_is_invalid varchar(255) ' + '''$.variable_is_invalid'',
estimate int ' + '''$.estimate'',
median int ' + '''$.median'',
statistic varchar(255) ' + '''$.statistic'',
rse int ' + '''$.rse'',
unreliable_estimate int ' + '''$.unreliable_estimate'',
decimal_display int ' + '''$.decimal_display'')'
execute(@sql)
end
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",
                "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": 13398,
                "median": null,
                "statistic": "TOTAL",
                "rse": 0,
                "unreliable_estimate": 0,
                "decimal_display": 0
            }]';
EXEC load_proc @json, 'table'
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

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