Insert JSON into SQL table-CodePudding

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?

CodePudding user response：

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