如何在INSERT ALL场景下避免向TEST表插入重复行？

问题场景 #

现有MY_TABLE表数据：

需要用INSERT ALL将MY_TABLE的最后三列插入TEST表（避免重复行），同时还要将NAME列插入另一张表，因此必须保留INSERT ALL语法且不能只选择最后三列。当前执行的语句因JOIN或原表重复导致TEST表插入两行重复数据。

解决方案 #

可以在INSERT ALL的查询部分做去重处理，保证插入TEST表的是唯一的(ID1, ID2, FLAG)组合，同时保留NAME列用于另一表的插入。

方案1：使用窗口函数去重 #

通过ROW_NUMBER()窗口函数按目标列分组，只取每组的第一行，可灵活控制保留哪一行的NAME值：

INSERT ALL
  -- 插入TEST表的逻辑
  WHEN ID1 IS NOT NULL AND FLAG THEN 
    INTO TEST VALUES (ID1, ID2, FLAG)
  -- 插入另一表的逻辑（假设表名为OTHER_TABLE，列名为NAME）
  INTO OTHER_TABLE VALUES (NAME)
SELECT *
FROM (
    SELECT mt.*,
           -- 按ID1、ID2、FLAG分组，优先保留非空NAME的行
           ROW_NUMBER() OVER (PARTITION BY mt.ID1, mt.ID2, mt.FLAG ORDER BY mt.NAME NULLS LAST) AS rn
    FROM MY_TABLE mt
    LEFT JOIN TEMP ON mt.ID1 = TEMP.ID1 -- 补充完整JOIN条件，这里假设TEMP关联列是ID1
) sub
WHERE sub.rn = 1;

方案2：使用GROUP BY聚合去重 #

如果NAME的具体取值不影响另一表的插入（比如只需保留非空NAME），可以用GROUP BY结合聚合函数简化逻辑：

INSERT ALL
  WHEN ID1 IS NOT NULL AND FLAG THEN 
    INTO TEST VALUES (ID1, ID2, FLAG)
  INTO OTHER_TABLE VALUES (NAME)
SELECT 
  MAX(NAME) AS NAME, -- 取非空的NAME值，MAX会忽略NULL
  ID1,
  ID2,
  FLAG
FROM MY_TABLE
LEFT JOIN TEMP ON MY_TABLE.ID1 = TEMP.ID1
GROUP BY ID1, ID2, FLAG;

说明 #

• 两种方案都在查询阶段完成去重，无需修改INSERT ALL的分支逻辑，满足必须保留NAME列插入另一表的要求。
• 注意修正原语句中LEFT JOIN TEMP ON ID1的语法问题，需明确关联的列（比如MY_TABLE.ID1 = TEMP.ID1）。

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