我正在从事SQL Server 2008的工作。我必须从customer表生成json数据并插入到CustomerJSON
表中。SQL Server 2016具有原生JSON功能,但不幸的是,我只能使用SQL Server 2008。
我使用了以下SQL语句:
Insert into CustomerJSON (Email, CustomerData)
select
Email,
stuff((select ',' + '{"email":' + email +
',"firstName":"' + FirstName + '"' +
',"lastName":"' + LastName + '"' +
',"phoneNumber":"' + PhoneNumber+ '"' +'}'
from Customer
for xml path('')), 1, 1, '')
from
Customer
正如您在屏幕截图中看到的,生成的JSON包含所有四行的数据。但是,我希望从Customer表的每一行构造JSON,而不是连接所有行。作为参考,我使用了SQL Server SELECT to JSON function
发布于 2019-04-15 02:19:41
您可以使用:
Insert into CustomerJSON (Email, CustomerData)
select Email , STUFF((
select ',' +
'{"email":' + email
+ ',"firstName":"' + FirstName + '"'
+ ',"lastName":"' + LastName + '"'
+ ',"phoneNumber":"' + LastName + '"'
+'}'
from Customer c2
where c2.Customerid = c.Customerid -- correlation
for xml path('')),1,1,'')
from Customer c;
如果CustomerId是PK(很可能),那么您不需要STUFF and FOR XML
,而是简单地使用+
连接,并使用ISNULL
处理可为空的列
Insert into CustomerJSON (Email, CustomerData)
select Email ,
'{"email":' + email
+ ',"firstName":"' + FirstName + '"'
+ ',"lastName":"' + LastName + '"'
+ ',"phoneNumber":"' + LastName + '"'
+'}'
from Customer c;
https://stackoverflow.com/questions/55678357
复制相似问题