Insert JSON into SQL table

Question

I have the following SQL Table 1:

id	name	gender	age	country	ambition
1	Peter	Male	20	Italy	Doctor
2	Angeli	Female	30	Australia	Lawyer

I want to insert into another table like this method. Output : SQL Table 2

id	name	details json
1	Peter	{"gender":"Male","age":"20","country":"Italy","ambition":"Doctor"}
2	Angeli	{"gender":"Female","age":"30","country":"Australia","ambition":"Lawyer"}

Any suggestions on how to insert multiple records?

What are you trying to do? Why did you want to return what looked like flag fields as key-value pairs in SQL? The actual question matters a lot. key=value, key=value is a terrible format. SQL can produce XML or JSON that can be easily parsed by any client. Assuming you need to combine flags into a single value in the first place. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 14, 2023 at 8:28
Besides, SQL Server has sparse columns since 2005 and can return all non-null values in a row using column sets. Whatever you were trying to do may already be available — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 14, 2023 at 8:30

Zhorov · Accepted Answer · 2023-01-27 07:52:42Z

4

For all versions, starting from SQL Server 2016, you may generate the JSON content for each row using FOR JSON PATH:

SELECT 
   id, name,
   details = (SELECT gender, age,  country, ambition FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
INTO NewTable
FROM OldTable

Starting from SQL Server 2022, you may use JSON_OBJECT():

SELECT 
   id, name,
   details = JSON_OBJECT('gender': gender, 'age': age, 'country': country, 'ambition': ambition)
INTO NewTable
FROM OldTable

answered Jan 27, 2023 at 7:21

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

1 Answer 1