时间戳

首页 > 解决方案 > 从 json 插入 sql server 表（2016）

sql - 从 json 插入 sql server 表（2016）

问题描述

我有一些这样的json： 

SET @json =N'[  
       { "id" : 2,"name": "John", "surname": "Smith", "lastname":"", "age": 25 },  

]'

我需要插入到表中，条件如下IF LASTNAME IS EMPTY IN JSON THEN INSERT SURNAME INTO LASTNAME ELSE INSERT LASTNAME INTO LASTNAME 

insert into mytable (id,firstname,lastname,age)
select id,name,case statement,age from openjson    
WITH (id int 'strict $.id',name nvarchar(100) '$.name',case statement, age int '$.age');

标签： sqlsql-server

解决方案

嗨，我认为这个查询可以响应：

DECLARE @json varchar(MAX)
SET @json =N'[  
       { "id" : 2,"name": "John", "surname": "Smith", "lastname":"", "age": 25 },
       { "id" : 2,"name": "John", "surname": "Smith", "lastname":"TT", "age": 25 },
       { "id" : 2,"name": "John", "surname": "Smith", "lastname":"TEST", "age": 25 }
]'  

select id,name,case when lastname = '' then surname else lastname end as lastnameup, age from openjson(@json)    
WITH (id int 'strict $.id',name nvarchar(100) '$.name', surname nvarchar(100) '$.surname', lastname nvarchar(100) '$.lastname', age int '$.age');

编辑姓氏的提议感谢@Shnugo

推荐阅读