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, Apr 14 '19 at 18:18

score 2 · Accepted Answer · answered Apr 14 '19 at 18:19

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;

The second solution worked perfectly. Looks like that I have been overthinking about it. Thank you!! — Massey, Apr 14 '19 at 18:36

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

1 Answers1