存储过程 将下列json解析并保存到临时表中。 {"ResultDataSet":[{"FirstInsPectionRecordDetailId":"","FirstInsPectionRecordId":"","SpecificationId":"SPE1000002B6","SpecificationName":"CSPI-PP","SpecificationResult":"OK","Specification_AssemblyId":"SFAB000000UT","CollectItemName":"1","MAX":null,"MIN":null,"Specification_AssemblyResult":"OK"},{"FirstInsPectionRecordDetailId":"","FirstInsPectionRecordId":"","SpecificationId":"SPE1000002BV","SpecificationName":"COB主流程开始","SpecificationResult":"OK","Specification_AssemblyId":"SFAB000000V0","CollectItemName":"ceshi1","MAX":1.0,"MIN":0.0,"Specification_AssemblyResult":"OK"},{"FirstInsPectionRecordDetailId":"","FirstInsPectionRecordId":"","SpecificationId":"SPE1000002BV","SpecificationName":"COB主流程开始","SpecificationResult":"OK","Specification_AssemblyId":"SFAB000000V1","CollectItemName":"2","MAX":null,"MIN":null,"Specification_AssemblyResult":"OK"},{"FirstInsPectionRecordDetailId":"","FirstInsPectionRecordId":"","SpecificationId":"SPE1000002BV","SpecificationName":"COB主流程开始","SpecificationResult":"OK","Specification_AssemblyId":"SFAB000000VA","CollectItemName":"123","MAX":123.00,"MIN":12.00,"Specification_AssemblyResult":"OK"}]}
时间: 2023-07-08 19:52:33 浏览: 86
您可以使用以下存储过程将JSON数据解析并保存到临时表中:
```
CREATE PROCEDURE parse_json_to_temp_table
@json nvarchar(max)
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp;
CREATE TABLE #tmp (
FirstInsPectionRecordDetailId nvarchar(50),
FirstInsPectionRecordId nvarchar(50),
SpecificationId nvarchar(50),
SpecificationName nvarchar(50),
SpecificationResult nvarchar(50),
Specification_AssemblyId nvarchar(50),
CollectItemName nvarchar(50),
MAX float,
MIN float,
Specification_AssemblyResult nvarchar(50)
);
INSERT INTO #tmp (
FirstInsPectionRecordDetailId,
FirstInsPectionRecordId,
SpecificationId,
SpecificationName,
SpecificationResult,
Specification_AssemblyId,
CollectItemName,
MAX,
MIN,
Specification_AssemblyResult
)
SELECT
FirstInsPectionRecordDetailId,
FirstInsPectionRecordId,
SpecificationId,
SpecificationName,
SpecificationResult,
Specification_AssemblyId,
CollectItemName,
MAX,
MIN,
Specification_AssemblyResult
FROM OPENJSON(@json, '$.ResultDataSet') WITH (
FirstInsPectionRecordDetailId nvarchar(50) '$.FirstInsPectionRecordDetailId',
FirstInsPectionRecordId nvarchar(50) '$.FirstInsPectionRecordId',
SpecificationId nvarchar(50) '$.SpecificationId',
SpecificationName nvarchar(50) '$.SpecificationName',
SpecificationResult nvarchar(50) '$.SpecificationResult',
Specification_AssemblyId nvarchar(50) '$.Specification_AssemblyId',
CollectItemName nvarchar(50) '$.CollectItemName',
MAX float '$.MAX',
MIN float '$.MIN',
Specification_AssemblyResult nvarchar(50) '$.Specification_AssemblyResult'
);
SELECT * FROM #tmp;
END
```
您可以通过调用此存储过程并传递JSON字符串作为参数来将数据保存到临时表中。例如:
```
EXEC parse_json_to_temp_table '{"ResultDataSet":[{"FirstInsPectionRecordDetailId":"","FirstInsPectionRecordId":"","SpecificationId":"SPE1000002B6","SpecificationName":"CSPI-PP","SpecificationResult":"OK","Specification_AssemblyId":"SFAB000000UT","CollectItemName":"1","MAX":null,"MIN":null,"Specification_AssemblyResult":"OK"},{"FirstInsPectionRecordDetailId":"","FirstInsPectionRecordId":"","SpecificationId":"SPE1000002BV","SpecificationName":"COB主流程开始","SpecificationResult":"OK","Specification_AssemblyId":"SFAB000000V0","CollectItemName":"ceshi1","MAX":1.0,"MIN":0.0,"Specification_AssemblyResult":"OK"},{"FirstInsPectionRecordDetailId":"","FirstInsPectionRecordId":"","SpecificationId":"SPE1000002BV","SpecificationName":"COB主流程开始","SpecificationResult":"OK","Specification_AssemblyId":"SFAB000000V1","CollectItemName":"2","MAX":null,"MIN":null,"Specification_AssemblyResult":"OK"},{"FirstInsPectionRecordDetailId":"","FirstInsPectionRecordId":"","SpecificationId":"SPE1000002BV","SpecificationName":"COB主流程开始","SpecificationResult":"OK","Specification_AssemblyId":"SFAB000000VA","CollectItemName":"123","MAX":123.00,"MIN":12.00,"Specification_AssemblyResult":"OK"}]}'```
这将返回一个临时表,其中包含解析的JSON数据。
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)