Snowflake：如何在单查询中结合INSERT ALL实现ACTIVE列标记最新记录

第一步：添加ACTIVE列 #

原表缺少ACTIVE字段，需要先执行DDL添加该列：

ALTER TABLE MY_TABLE ADD COLUMN ACTIVE BOOLEAN NOT NULL DEFAULT FALSE;

方案一：增量更新（MERGE + INSERT ALL） #

如果需要保留原有历史数据，仅更新对应ID的旧记录状态并插入新记录，同时完成多表插入，可以用MERGE处理MY_TABLE的更新和插入，再配合INSERT ALL插入其他表：

-- 1. 预处理新数据，标记为ACTIVE=TRUE
WITH new_data AS (
    SELECT 
        LINK_KEY,
        TIME,
        DATASET_NAME,
        DATASET_DATE,
        ORDER_NUMBER,
        O_KEY AS ID,
        OA_KEY AS ATTRIBUTE_ID,
        TRUE AS ACTIVE
    FROM TEST_TABLE
    WHERE HAS_DATA AND ID_SEQ_NUM > 1
)
-- 2. 执行MERGE，更新旧记录状态并插入新记录
MERGE INTO MY_TABLE t
USING (
    -- 新插入的数据
    SELECT *, 'INSERT' AS op FROM new_data
    UNION ALL
    -- 需要更新状态的旧记录：同一ID下，ORDER小于新记录ORDER_NUMBER的设为INACTIVE
    SELECT 
        t.LINK_ID,
        t.LOAD,
        t.SOURCE,
        t.SOURCE_DATE,
        t.ORDER,
        t.ID,
        t.ATTRIBUTE_ID,
        FALSE AS ACTIVE,
        'UPDATE' AS op
    FROM MY_TABLE t
    JOIN new_data nd ON t.ID = nd.ID
    WHERE t.ORDER < nd.ORDER_NUMBER
) src
ON t.LINK_ID = src.LINK_ID
WHEN MATCHED AND src.op = 'UPDATE' THEN
    UPDATE SET ACTIVE = src.ACTIVE
WHEN NOT MATCHED AND src.op = 'INSERT' THEN
    INSERT (LINK_ID, LOAD, SOURCE, SOURCE_DATE, ORDER, ID, ATTRIBUTE_ID, ACTIVE)
    VALUES (src.LINK_KEY, src.TIME, src.DATASET_NAME, src.DATASET_DATE, src.ORDER_NUMBER, src.ID, src.ATTRIBUTE_ID, src.ACTIVE);

-- 3. 用INSERT ALL插入新数据到其他表
INSERT ALL
    INTO OTHER_TABLE1 (COL1, COL2, COL3) VALUES (LINK_KEY, TIME, DATASET_NAME)
    INTO OTHER_TABLE2 (COL_A, COL_B) VALUES (O_KEY, OA_KEY)
SELECT * FROM new_data;

方案二：全量覆盖（INSERT ALL + OVERWRITE） #

如果可以接受全量覆盖MY_TABLE（重新生成所有数据并标记ACTIVE），直接用INSERT ALL同时处理MY_TABLE的覆盖和其他表的插入：

INSERT ALL
    -- 覆盖MY_TABLE，全量生成带ACTIVE标记的数据
    OVERWRITE INTO MY_TABLE (LINK_ID, LOAD, SOURCE, SOURCE_DATE, ORDER, ID, ATTRIBUTE_ID, ACTIVE)
    VALUES (LINK_ID, LOAD, SOURCE, SOURCE_DATE, ORDER, ID, ATTRIBUTE_ID, ACTIVE)
    -- 插入新数据到其他表
    INTO OTHER_TABLE1 (COL1, COL2, COL3) VALUES (nt.LINK_KEY, nt.TIME, nt.DATASET_NAME)
    INTO OTHER_TABLE2 (COL_A, COL_B) VALUES (nt.O_KEY, nt.OA_KEY)
-- 合并原有数据和新数据，计算ACTIVE标记
SELECT 
    mt.LINK_ID,
    mt.LOAD,
    mt.SOURCE,
    mt.SOURCE_DATE,
    mt.ORDER,
    mt.ID,
    mt.ATTRIBUTE_ID,
    -- 每个ID组中ORDER最大的记录标记为ACTIVE=TRUE
    CASE WHEN mt.ORDER = MAX(mt.ORDER) OVER (PARTITION BY mt.ID) THEN TRUE ELSE FALSE END AS ACTIVE
FROM MY_TABLE mt
UNION ALL
SELECT 
    nt.LINK_KEY,
    nt.TIME,
    nt.DATASET_NAME,
    nt.DATASET_DATE,
    nt.ORDER_NUMBER,
    nt.O_KEY,
    nt.OA_KEY,
    -- 新数据的ORDER_NUMBER为当前ID的最大值，直接标记为TRUE
    TRUE AS ACTIVE
FROM TEST_TABLE nt
WHERE HAS_DATA AND ID_SEQ_NUM > 1;

关键说明 #

• 方案一适合增量场景，仅更新必要的旧记录，性能更优；方案二适合全量同步场景，逻辑更简单。
• 若LINK_ID不是唯一键，需要调整MERGE的匹配条件（比如用ID+ORDER组合），避免更新错误。

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