如何在SQL中将temp_table全字段Upsert至master_table？

要实现将temp_table的记录Upsert到master_table（匹配_id的记录更新，不匹配的插入），且两表列数一致、包含20+字段，以下是主流数据库的具体实现方案：

原始表结构与数据 #

master_table

temp_table

期望执行结果 #

（注：原示例期望结果遗漏了_id=245的新记录，根据Upsert目标补充完整）

MySQL / MariaDB #

使用INSERT ... ON DUPLICATE KEY UPDATE语法，依赖_id为主键或唯一约束。针对多字段场景，可通过动态SQL自动生成更新语句，避免手动编写20+列名：

静态SQL（适用于字段固定且数量少的场景） #

INSERT INTO master_table
SELECT * FROM temp_table
ON DUPLICATE KEY UPDATE
    zipcode = VALUES(zipcode),
    col1 = VALUES(col1),
    col2 = VALUES(col2),
    -- 按此格式补充剩余字段
    ...

动态SQL（适用于20+字段的场景） #

SET @sql = (
    SELECT CONCAT(
        'INSERT INTO master_table SELECT * FROM temp_table ON DUPLICATE KEY UPDATE ',
        GROUP_CONCAT(COLUMN_NAME, ' = VALUES(', COLUMN_NAME, ')')
    )
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'master_table'
      AND COLUMN_NAME != '_id'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL #

使用INSERT ... ON CONFLICT语法，通过EXCLUDED引用临时表的待插入字段：

静态SQL #

INSERT INTO master_table
SELECT * FROM temp_table
ON CONFLICT (_id) DO UPDATE SET
    zipcode = EXCLUDED.zipcode,
    col1 = EXCLUDED.col1,
    col2 = EXCLUDED.col2,
    -- 补充剩余字段
    ...

动态SQL #

DO $$
DECLARE
    update_cols text;
BEGIN
    SELECT string_agg(format('%I = EXCLUDED.%I', column_name, column_name), ', ')
    INTO update_cols
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'master_table'
      AND column_name != '_id';

    EXECUTE format(
        'INSERT INTO master_table SELECT * FROM temp_table ON CONFLICT (_id) DO UPDATE SET %s',
        update_cols
    );
END $$;

SQL Server #

使用MERGE语句完成匹配更新与插入：

静态SQL #

MERGE INTO master_table AS target
USING temp_table AS source
ON target._id = source._id
WHEN MATCHED THEN
    UPDATE SET
        target.zipcode = source.zipcode,
        target.col1 = source.col1,
        target.col2 = source.col2,
        -- 补充剩余字段
        ...
WHEN NOT MATCHED THEN
    INSERT (_id, zipcode, col1, col2, ...)
    VALUES (source._id, source.zipcode, source.col1, source.col2, ...);

动态SQL #

DECLARE @update_cols NVARCHAR(MAX), @insert_cols NVARCHAR(MAX);

SELECT @update_cols = STRING_AGG(QUOTENAME(COLUMN_NAME) + ' = source.' + QUOTENAME(COLUMN_NAME), ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
  AND TABLE_NAME = 'master_table'
  AND COLUMN_NAME != '_id';

SELECT @insert_cols = STRING_AGG(QUOTENAME(COLUMN_NAME), ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
  AND TABLE_NAME = 'master_table';

DECLARE @sql NVARCHAR(MAX) = N'
MERGE INTO master_table AS target
USING temp_table AS source
ON target._id = source._id
WHEN MATCHED THEN
    UPDATE SET ' + @update_cols + '
WHEN NOT MATCHED THEN
    INSERT (' + @insert_cols + ')
    VALUES (source.' + REPLACE(@insert_cols, ', ', ', source.') + ');';

EXEC sp_executesql @sql;

Oracle #

使用MERGE语句实现Upsert逻辑：

静态SQL #

MERGE INTO master_table target
USING temp_table source
ON (target._id = source._id)
WHEN MATCHED THEN
    UPDATE SET
        target.zipcode = source.zipcode,
        target.col1 = source.col1,
        target.col2 = source.col2,
        -- 补充剩余字段
        ...
WHEN NOT MATCHED THEN
    INSERT (_id, zipcode, col1, col2, ...)
    VALUES (source._id, source.zipcode, source.col1, source.col2, ...);

动态SQL #

DECLARE
    update_cols VARCHAR2(4000);
    insert_cols VARCHAR2(4000);
BEGIN
    SELECT LISTAGG(column_name || ' = source.' || column_name, ', ') WITHIN GROUP (ORDER BY column_id)
    INTO update_cols
    FROM user_tab_columns
    WHERE table_name = 'MASTER_TABLE'
      AND column_name != '_ID';

    SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id)
    INTO insert_cols
    FROM user_tab_columns
    WHERE table_name = 'MASTER_TABLE';

    EXECUTE IMMEDIATE '
        MERGE INTO master_table target
        USING temp_table source
        ON (target._id = source._id)
        WHEN MATCHED THEN
            UPDATE SET ' || update_cols || '
        WHEN NOT MATCHED THEN
            INSERT (' || insert_cols || ')
            VALUES (source.' || REPLACE(insert_cols, ', ', ', source.') || ')';
END;
/

注意事项 #

1. 必须确保_id是master_table的主键或唯一约束，否则无法正确匹配记录执行Upsert。
2. 动态SQL版本适用于字段较多的场景，可避免手动编写大量列名，降低出错概率。
3. 执行前建议在测试环境验证逻辑，或备份目标表数据，防止数据异常。

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