Oracle SQL：如何从其他表按条件批量插入多行数据

核心问题修正 #

你尝试的插入语句存在语法错误：使用INSERT...SELECT批量插入时，不需要用VALUES()包裹查询语句，这是导致语句执行失败的直接原因。

正确的批量插入语句 #

写法1：省略目标表列名（需保证SELECT列顺序与目标表完全一致） #

INSERT INTO MARKETING_COMMODITY
SELECT A.DTIME_SIGNATURE 
, A.AMT_SIGNED  
, A.CNT_SIGNED
, A.APPLICATION_AMOUNT
, A.PRODUCT 
, A.PRODUCT_TYPE 
, A.PRODUCT_PRICE 
, VSR.NAME_PRODUCER
, vsr.text_model_number
, vsp.partner_name 
, vsp.partner_brand 
, vspl.salesroom 
, vspl.mall 

FROM DM_SALES.V_SALES_DM_DATA A 

LEFT JOIN DM_SALES.V_SALES_DM_PARTNER VSP ON A.CODE_SALESROOM_PARTNER = VSP.CODE_SALESROOM_PARTNER 
LEFT JOIN dm_sales.v_sales_dm_pos_list vspl on a.code_salesroom = vspl.code_salesroom 
LEFT JOIN DM_SALES.V_SALES_DM_CONTRACT_BUNDLE VSR ON A.CONTRACT_NUMBER = VSR.CONTRACT_NUMBER 

WHERE 1=1
  AND a.contract_state <> 'Cancelled' 
  AND a.cnt_signed=1 
  AND A.LOAN_TYPE = 'Consumer Loan'
  AND TRUNC(A.DTIME_SIGNATURE) BETWEEN DATE'2022-09-01' AND TRUNC(SYSDATE); -- 自动更新到当前日期

写法2：显式指定目标表列名（更安全，避免列顺序变更导致错误） #

INSERT INTO MARKETING_COMMODITY(
    DTIME_SIGNATURE,
    AMT_SIGNED,
    CNT_SIGNED,
    APPLICATION_AMOUNT,
    PRODUCT,
    PRODUCT_TYPE,
    PRODUCT_PRICE,
    NAME_PRODUCER,
    TEXT_MODEL_NUMBER,
    PARTNER_NAME,
    PARTNER_BRAND,
    SALESROOM,
    MALL
)
SELECT A.DTIME_SIGNATURE 
, A.AMT_SIGNED  
, A.CNT_SIGNED
, A.APPLICATION_AMOUNT
, A.PRODUCT 
, A.PRODUCT_TYPE 
, A.PRODUCT_PRICE 
, VSR.NAME_PRODUCER
, vsr.text_model_number
, vsp.partner_name 
, vsp.partner_brand 
, vspl.salesroom 
, vspl.mall 

FROM DM_SALES.V_SALES_DM_DATA A 

LEFT JOIN DM_SALES.V_SALES_DM_PARTNER VSP ON A.CODE_SALESROOM_PARTNER = VSP.CODE_SALESROOM_PARTNER 
LEFT JOIN dm_sales.v_sales_dm_pos_list vspl on a.code_salesroom = vspl.code_salesroom 
LEFT JOIN DM_SALES.V_SALES_DM_CONTRACT_BUNDLE VSR ON A.CONTRACT_NUMBER = VSR.CONTRACT_NUMBER 

WHERE 1=1
  AND a.contract_state <> 'Cancelled' 
  AND a.cnt_signed=1 
  AND A.LOAN_TYPE = 'Consumer Loan'
  AND TRUNC(A.DTIME_SIGNATURE) BETWEEN DATE'2022-09-01' AND TRUNC(SYSDATE);

增量更新优化（避免重复插入） #

如果后续需要定期同步数据到当前日期，建议添加重复数据过滤逻辑，防止重复插入相同记录：

方式1：用NOT EXISTS过滤已存在数据 #

INSERT INTO MARKETING_COMMODITY(
    DTIME_SIGNATURE,
    AMT_SIGNED,
    CNT_SIGNED,
    APPLICATION_AMOUNT,
    PRODUCT,
    PRODUCT_TYPE,
    PRODUCT_PRICE,
    NAME_PRODUCER,
    TEXT_MODEL_NUMBER,
    PARTNER_NAME,
    PARTNER_BRAND,
    SALESROOM,
    MALL
)
SELECT A.DTIME_SIGNATURE 
, A.AMT_SIGNED  
, A.CNT_SIGNED
, A.APPLICATION_AMOUNT
, A.PRODUCT 
, A.PRODUCT_TYPE 
, A.PRODUCT_PRICE 
, VSR.NAME_PRODUCER
, vsr.text_model_number
, vsp.partner_name 
, vsp.partner_brand 
, vspl.salesroom 
, vspl.mall 

FROM DM_SALES.V_SALES_DM_DATA A 

LEFT JOIN DM_SALES.V_SALES_DM_PARTNER VSP ON A.CODE_SALESROOM_PARTNER = VSP.CODE_SALESROOM_PARTNER 
LEFT JOIN dm_sales.v_sales_dm_pos_list vspl on a.code_salesroom = vspl.code_salesroom 
LEFT JOIN DM_SALES.V_SALES_DM_CONTRACT_BUNDLE VSR ON A.CONTRACT_NUMBER = VSR.CONTRACT_NUMBER 

WHERE 1=1
  AND a.contract_state <> 'Cancelled' 
  AND a.cnt_signed=1 
  AND A.LOAN_TYPE = 'Consumer Loan'
  AND TRUNC(A.DTIME_SIGNATURE) BETWEEN DATE'2022-09-01' AND TRUNC(SYSDATE)
  -- 假设DTIME_SIGNATURE+CONTRACT_NUMBER是唯一标识，根据实际业务调整
  AND NOT EXISTS (
      SELECT 1 FROM MARKETING_COMMODITY mc
      WHERE mc.DTIME_SIGNATURE = A.DTIME_SIGNATURE
        AND mc.CONTRACT_NUMBER = A.CONTRACT_NUMBER
  );

方式2：用MERGE实现插入/更新二合一 #

如果存在相同主键的记录需要更新（而非仅插入新记录），可以使用MERGE语句：

MERGE INTO MARKETING_COMMODITY mc
USING (
    SELECT A.DTIME_SIGNATURE 
    , A.AMT_SIGNED  
    , A.CNT_SIGNED
    , A.APPLICATION_AMOUNT
    , A.PRODUCT 
    , A.PRODUCT_TYPE 
    , A.PRODUCT_PRICE 
    , VSR.NAME_PRODUCER
    , vsr.text_model_number
    , vsp.partner_name 
    , vsp.partner_brand 
    , vspl.salesroom 
    , vspl.mall 
    , A.CONTRACT_NUMBER -- 用于匹配唯一记录的字段
    FROM DM_SALES.V_SALES_DM_DATA A 

    LEFT JOIN DM_SALES.V_SALES_DM_PARTNER VSP ON A.CODE_SALESROOM_PARTNER = VSP.CODE_SALESROOM_PARTNER 
    LEFT JOIN dm_sales.v_sales_dm_pos_list vspl on a.code_salesroom = vspl.code_salesroom 
    LEFT JOIN DM_SALES.V_SALES_DM_CONTRACT_BUNDLE VSR ON A.CONTRACT_NUMBER = VSR.CONTRACT_NUMBER 

    WHERE 1=1
      AND a.contract_state <> 'Cancelled' 
      AND a.cnt_signed=1 
      AND A.LOAN_TYPE = 'Consumer Loan'
      AND TRUNC(A.DTIME_SIGNATURE) BETWEEN DATE'2022-09-01' AND TRUNC(SYSDATE)
) src
ON (mc.DTIME_SIGNATURE = src.DTIME_SIGNATURE AND mc.CONTRACT_NUMBER = src.CONTRACT_NUMBER)
WHEN MATCHED THEN
    UPDATE SET 
        mc.AMT_SIGNED = src.AMT_SIGNED,
        mc.CNT_SIGNED = src.CNT_SIGNED,
        mc.APPLICATION_AMOUNT = src.APPLICATION_AMOUNT,
        mc.PRODUCT = src.PRODUCT
        -- 按需添加其他需要更新的字段
WHEN NOT MATCHED THEN
    INSERT (
        DTIME_SIGNATURE,
        AMT_SIGNED,
        CNT_SIGNED,
        APPLICATION_AMOUNT,
        PRODUCT,
        PRODUCT_TYPE,
        PRODUCT_PRICE,
        NAME_PRODUCER,
        TEXT_MODEL_NUMBER,
        PARTNER_NAME,
        PARTNER_BRAND,
        SALESROOM,
        MALL
    ) VALUES (
        src.DTIME_SIGNATURE,
        src.AMT_SIGNED,
        src.CNT_SIGNED,
        src.APPLICATION_AMOUNT,
        src.PRODUCT,
        src.PRODUCT_TYPE,
        src.PRODUCT_PRICE,
        src.NAME_PRODUCER,
        src.TEXT_MODEL_NUMBER,
        src.PARTNER_NAME,
        src.PARTNER_BRAND,
        src.SALESROOM,
        src.MALL
    );

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