兼容SQL Server/Oracle/PostgreSQL的跨表拼接插入SQL如何实现？

最优方案说明 #

符合SQL:2016标准的STRING_AGG聚合函数是当前兼容性最高的实现方式，SQL Server 2017+、Oracle 19c+、PostgreSQL 9.4+均原生支持，无需针对不同数据库编写分支逻辑。该函数会按照指定排序规则将多行的text字段值拼接为单个字符串，完全匹配需求。

通用SQL脚本 #

INSERT INTO DEST_TABLE ("key", name, "value")
SELECT
    1 AS "key",
    'concatenated strings' AS name,
    STRING_AGG(text, '' ORDER BY id ASC) AS "value"
FROM TEMP_TABLE;

注意：key和value属于部分数据库的保留关键字，添加双引号包裹可避免关键字冲突，三种数据库均支持双引号标记标识符。

低版本兼容方案 #

如果使用的数据库版本低于STRING_AGG支持的最低版本，可替换为对应数据库的专属拼接函数，仅需修改聚合部分逻辑：

• Oracle 11gR2 ~ 18c：将STRING_AGG(text, '' ORDER BY id ASC)替换为LISTAGG(text, '') WITHIN GROUP (ORDER BY id ASC)
• SQL Server 2008 ~ 2016：需使用FOR XML PATH语法实现，该写法不支持跨库兼容，仅适用于SQL Server环境
• PostgreSQL 9.3及以下：可使用array_to_string(array_agg(text ORDER BY id ASC), '')替代，PostgreSQL全版本支持该写法

注意事项 #

• 必须显式在拼接函数中指定ORDER BY id ASC，否则数据库不保证拼接顺序与id顺序一致
• 拼接后的字符串长度不能超过目标表value字段的长度上限，超长场景建议将字段类型调整为大文本类型存储（Oracle CLOB、SQL Server TEXT/ VARCHAR(max)、PostgreSQL TEXT）
• 三种数据库的拼接函数默认均会忽略text为NULL的行，不会将NULL值转换为字符串拼接进结果

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