如何将SQL表1数据批量插入为含JSON字段的SQL表2？

假设Table1和Table2的结构已创建完成：

• Table1：id, name, gender, age, country, ambition
• Table2：id, name, details json（JSON类型字段）

下面针对主流数据库提供批量插入的SQL语句：

1. MySQL/MariaDB #

利用JSON_OBJECT()函数构造JSON对象，通过INSERT ... SELECT完成批量插入：

INSERT INTO Table2 (id, name, `details json`)
SELECT 
    id,
    name,
    JSON_OBJECT(
        'gender', gender,
        'age', age,
        'country', country,
        'ambition', ambition
    ) AS `details json`
FROM Table1;

2. PostgreSQL #

使用json_build_object()生成JSON对象：

INSERT INTO Table2 (id, name, "details json")
SELECT 
    id,
    name,
    json_build_object(
        'gender', gender,
        'age', age,
        'country', country,
        'ambition', ambition
    ) AS "details json"
FROM Table1;

若需更高效的JSONB存储格式，可替换为jsonb_build_object()。

3. SQL Server #

方法一：手动拼接JSON（适合字段较少场景） #

需注意处理字段中的双引号，避免格式错误：

INSERT INTO Table2 (id, name, [details json])
SELECT 
    id,
    name,
    JSON_QUERY(
        '{' +
        '"gender":"' + REPLACE(gender, '"', '\"') + '",' +
        '"age":"' + CAST(age AS VARCHAR) + '",' +
        '"country":"' + REPLACE(country, '"', '\"') + '",' +
        '"ambition":"' + REPLACE(ambition, '"', '\"') + '"' +
        '}'
    ) AS [details json]
FROM Table1;

方法二：FOR JSON PATH（自动处理特殊字符，更安全） #

INSERT INTO Table2 (id, name, [details json])
SELECT 
    id,
    name,
    (SELECT gender, age, country, ambition FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS [details json]
FROM Table1;

WITHOUT_ARRAY_WRAPPER确保返回单个JSON对象而非数组，符合需求。

4. Oracle（12c及以上） #

使用JSON_OBJECT()函数构造JSON：

INSERT INTO Table2 (id, name, "details json")
SELECT 
    id,
    name,
    JSON_OBJECT(
        'gender' VALUE gender,
        'age' VALUE age,
        'country' VALUE country,
        'ambition' VALUE ambition
    ) AS "details json"
FROM Table1;

内容的提问来源于stack exchange，提问作者Shan