时间戳

首页 > 解决方案 > Sql - 打开 Json 插入表

sql - Sql - 打开 Json 插入表

问题描述

我有一部分存储过程检索从我的 Json 接收到的数据，然后我使用了 OPENJSON 以便以表格格式显示。

但是如何将它插入到现有表中，在我的 OpenJson 中具有相同的声明列名？

 Declare @JsonString varchar(MAX) = (Select DataReceived from ItemTransaction where 
                        DocType ='LoadItem' and BranchCode = @pBranchCode and TranNo =  @pTranNo)


Sample ReplyData string
'{"status":{"code":"0","name":"RCRD_LOADED","status":"Success","message":"Record sucessfully loaded"},"records":[{"recordtype":"inventoryitem","id":"2310","itemid":"00999","displayname":"Neck - Tie","locationquantityonhand":"9996","locationquantityavailable":"9994","locationquantitycommitted":"9994","locationquantitybackordered":"9994"}]}'


SELECT * FROM  
OPENJSON ( @JsonString,'$."records"')  
WITH (   
              RecordType   varchar(200) '$.recordtype' ,  
              ItemID     varchar     '$.id',  
              ItemDetails varchar(200) '$.itemid',  
              Quantity int          '$.locationquantityonhand'  
 ) 
 end

标签： sqljson

解决方案

您可以拥有与 OPENSJSON 具有相似架构的表并将其插入。


CREATE TABLE TestJson
(
RecordType   varchar(200) 
,ItemID     varchar     
,ItemDetails varchar(200)   
,Quantity int               
)

DECLARE @JsonString VARCHAR(MAX) = 
'{"status":{"code":"0","name":"RCRD_LOADED","status":"Success","message":"Record sucessfully loaded"},"records":[{"recordtype":"inventoryitem","id":"2310","itemid":"00999","displayname":"Neck - Tie","locationquantityonhand":"9996","locationquantityavailable":"9994","locationquantitycommitted":"9994","locationquantitybackordered":"9994"}]}'


INSERT INTO TestJson
SELECT * FROM  
OPENJSON ( @JsonString,'$."records"')  
WITH (   
              RecordType   varchar(200) '$.recordtype' ,  
              ItemID     varchar        '$.id',  
              ItemDetails varchar(200)  '$.itemid',  
              Quantity int              '$.locationquantityonhand'  
 ) 

 SELECT * FROM TestJson


+---------------+--------+-------------+----------+
|  RecordType   | ItemID | ItemDetails | Quantity |
+---------------+--------+-------------+----------+
| inventoryitem |      2 |       00999 |     9996 |
+---------------+--------+-------------+----------+

推荐阅读