Snowflake SQL能否在values子句外使用CASE WHEN实现条件插入

你期望的「在VALUES子句外部用CASE WHEN包裹INSERT语句、按分支执行插入」的写法在Snowflake中不支持。
Snowflake里的CASE WHEN本质是值表达式，只能用来返回字段/变量值，不能把DML语句（比如INSERT）直接放在THEN/ELSE分支里执行。

以下是问题中用到的测试表创建逻辑：

-- 创建原始表并插入测试数据
create temporary table invoice_original (id integer, price number(12,2),
                                         purpose varchar);
insert into invoice_original (id, price, purpose) values
  (1, 11.11, 'Business'),
  (2, 22.22, 'Personal'),
  (3, 33.33, 'Business'),
  (4, 44.44, 'Personal'),
  (5, 55.55, 'Business');
  
  
-- 创建空的结果表
create temporary table invoice_final (
  study_number varchar,
  price number(12, 2),
  price_type varchar
);

可正常运行的旧写法 #

通过两次INSERT+WHERE条件过滤实现插入，逻辑正确但存在冗余：

execute immediate $$
declare
  new_price number(12,2);
  new_purpose varchar;
  c1 cursor for select price, purpose from invoice_original;
begin
  for record in c1 do
        new_price := record.price;
        new_purpose := record.purpose;
                
       insert into invoice_final(study_number, price, price_type)
       select 1, :new_price, 'Dollars'
       where :new_purpose ilike '%Business%';

       insert into invoice_final(study_number, price, price_type)
       select 2, :new_price, 'Dollars'
       where :new_purpose not like '%Business%';
  end for;
end;
$$;

无法运行的目标写法 #

试图用CASE WHEN分支直接包裹INSERT语句，不符合Snowflake语法规则：

execute immediate $$
declare
  new_price number(12,2);
  new_purpose varchar;
  c1 cursor for select price, purpose from invoice_original;
begin
  for record in c1 do
        new_price := record.price;
        new_purpose := record.purpose;

        -- 以下写法语法不支持
        CASE  
        WHEN :new_purpose ilike '%Business%' then 
        insert into invoice_final(study_number, price, price_type) 
        values('1', :new_price, 'Dollars')
        ELSE 
        insert into invoice_final(study_number, price, price_type) 
        values('2', :new_price, 'Dollars') END
        
  end for;
end;
$$;

推荐优先用第一种方案，性能远高于游标循环写法：

• 方案1：无循环单条插入（最优）
  把CASE WHEN放到SELECT子句里做值判断，一条语句完成全量插入，不需要写存储过程、不需要游标，大数据量下性能最好：

  INSERT INTO invoice_final(study_number, price, price_type)
  SELECT 
    CASE WHEN purpose ILIKE '%Business%' THEN '1' ELSE '2' END AS study_number,
    price,
    'Dollars' AS price_type
  FROM invoice_original;
  

• 方案2：存储过程内分支执行（适配必须用循环的场景）
  如果业务逻辑必须保留游标循环，用Snowflake Scripting原生的IF/ELSE流控语句做分支，不要用CASE WHEN包裹INSERT：

  execute immediate $$
  declare
    new_price number(12,2);
    new_purpose varchar;
    c1 cursor for select price, purpose from invoice_original;
  begin
    for record in c1 do
          new_price := record.price;
          new_purpose := record.purpose;
          
          IF new_purpose ILIKE '%Business%' THEN
            insert into invoice_final(study_number, price, price_type) 
            values('1', :new_price, 'Dollars');
          ELSE
            insert into invoice_final(study_number, price, price_type) 
            values('2', :new_price, 'Dollars');
          END IF;
    end for;
  end;
  $$;
  

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