All we need is an easy explanation of the problem, so here it is.
I have the a result of a FOR JSON expression, and I’d like to insert it into a table column.
How do I do that?
Here is one of my unsuccessful trials (note that the SELECT hightrees ... FOR JSON PATH part is correct, and can be seen in the answer to my other question, and here’s also the live demo), which gives the error Invalid object name 'TreesJson':
SELECT * FROM (
SELECT
highTrees = JSON_QUERY(
(
SELECT
Id as id,
Type as type,
Height as height
FROM Trees WHERE [Height] > 5
FOR JSON PATH
)
),
lowTrees = JSON_QUERY(
(
SELECT
Id as id,
Type as type,
Height as height
FROM Trees WHERE [Height] < 1
FOR JSON PATH
)
)
FOR JSON
PATH, WITHOUT_ARRAY_WRAPPER
) AS TreesJson;
INSERT INTO TreesGrowthLog ([Day], [TreesGrowth])
VALUES (CAST(GETDATE() AS Date, (SELECT * FROM TreesJson FOR JSON AUTO))
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
Here is a way of doing this:
INSERT INTO TreesGrowthLog ([Day], [TreesGrowth])
SELECT
CAST(GETDATE() AS Date),
(
SELECT *
FROM
(
SELECT TreesJson.TreesJson
FROM (
SELECT
highTrees = JSON_QUERY(
(
SELECT Id as id, Type as type, Height as height
FROM Trees WHERE [Height] > 5
FOR JSON PATH
)
),
lowTrees = JSON_QUERY(
(
SELECT Id as id, Type as type, Height as height
FROM Trees WHERE [Height] < 1
FOR JSON PATH
)
)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS TreesJson(TreesJson)
) as a FOR JSON AUTO
)
with values way
INSERT INTO TreesGrowthLog ([Day], [TreesGrowth])
VALUES(
CAST(GETDATE() AS Date),
(
SELECT TreesJson.TreesJson
FROM (
SELECT
highTrees = JSON_QUERY(
(
SELECT Id as id, Type as type, Height as height
FROM Trees WHERE [Height] > 5
FOR JSON PATH
)
),
lowTrees = JSON_QUERY(
(
SELECT Id as id, Type as type, Height as height
FROM Trees WHERE [Height] < 1
FOR JSON PATH
)
)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS TreesJson(TreesJson)
)
);
and with CTE version:
;with cte as
(
SELECT TreesJson.TreesJson
FROM (
SELECT
highTrees = JSON_QUERY(
(
SELECT Id as id, Type as type, Height as height
FROM Trees WHERE [Height] > 5
FOR JSON PATH
)
),
lowTrees = JSON_QUERY(
(
SELECT Id as id, Type as type, Height as height
FROM Trees WHERE [Height] < 1
FOR JSON PATH
)
)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS TreesJson(TreesJson)
)
INSERT INTO TreesGrowthLog ([Day], [TreesGrowth])
SELECT
CAST(GETDATE() AS Date), TreesJson
FROM cte
or use temp table #
SELECT *
INTO #tmp
FROM (
SELECT
highTrees = JSON_QUERY(
(
SELECT
Id as id,
Type as type,
Height as height
FROM Trees WHERE [Height] > 5
FOR JSON PATH
)
),
lowTrees = JSON_QUERY(
(
SELECT
Id as id,
Type as type,
Height as height
FROM Trees WHERE [Height] < 1
FOR JSON PATH
)
)
FOR JSON
PATH, WITHOUT_ARRAY_WRAPPER
) AS TreesJson(TreesJson);
SELECT * FROM #tmp;
INSERT INTO TreesGrowthLog ([Day], [TreesGrowth])
VALUES(
CAST(GETDATE() AS Date),(SELECT * FROM #tmp /*FOR JSON AUTO*/) )
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0