I have json data which have a list of productDetails. I need to combine each of the detail with productId.
I am using a stored procedure. This is my code:
DECLARE @products AS TABLE(productName NVARCHAR(255),
productId INT NOT NULL);
INSERT INTO @products
EXEC [saveproducts] @product;
From the above query, I will get the list of products as a table. Now I need to join the detail in the json with corresponding product
INSERT INTO @detail
SELECT
[detaiId], [productId]
FROM
OPENJSON(@detailJSON)
WITH(detailId UNIQUEIDENTIFIER 'strict $.detaiId',
productId INT ??????);
How to get the productId
by comparing the productName
in @detailJSON
and from table @products
?
Only a simple inner join is needed
INSERT INTO @detail
SELECT
J.[detaiId], P.[productId]
FROM
@products P
INNER JOIN
OPENJSON(@detailJSON)
WITH(detailId UNIQUEIDENTIFIER 'strict $.detaiId',
productName NVARCHAR(255) 'strict $.productName'
) J
ON J.productName = P.productName
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With