How to generate JSON in SQL Server 2008 and insert into another table

Question

I am working on SQL Server 2008. I have to generate json data from the customer table and insert into the CustomerJSON table. SQL Server 2016 has native JSON capabilities, but unfortunately, I have to work with SQL Server 2008 only.

I have used the following SQL statements:

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

As you can see in the screenshot, the JSON generated has all the four rows' data. However, I want JSON to be constructed from each row of the Customer table and not concatenate all. For reference, I used, SQL Server SELECT to JSON function

Turn your sub query in a correlated sub query; at the moment it's returnimg everyrow — Thom A
– Thom A ♦, Commented Apr 14, 2019 at 18:18

Lukasz Szozda · Accepted Answer · 2019-04-14 18:19:40Z

2

You could use:

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;

If CustomerId is PK(most likely) you don't need STUFF and FOR XML but simple concat with + and handling nullable columns with ISNULL:

Insert into CustomerJSON (Email, CustomerData)
select Email , 
        '{"email":' + email 
        + ',"firstName":"' + FirstName + '"'
        + ',"lastName":"' + LastName + '"'
        + ',"phoneNumber":"' + LastName + '"'
        +'}'
from  Customer c;

answered Apr 14, 2019 at 18:19

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Massey Over a year ago

The second solution worked perfectly. Looks like that I have been overthinking about it. Thank you!!

Collectives™ on Stack Overflow

How to generate JSON in SQL Server 2008 and insert into another table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related