Snowflake如何使用CTE输出结果执行INSERT INTO表数据插入操作？

核心实现逻辑 #

你提供的写法是Snowflake原生支持的标准DML语法，执行逻辑按顺序分为三步：

1. 先执行WITH子句定义名为ct2的公共表表达式：指定该临时结果集包含emp_name、emp_id两个字段，数据从TEST_1.PUBLIC.TEST11表中查询对应字段生成，该结果集仅在当前SQL执行周期内有效。
2. 声明插入操作的目标表为TEST_1.PUBLIC.EMP1。
3. 从预先定义好的ct2 CTE中查询对应字段，将返回的所有行批量写入目标表中。

这种写法将数据逻辑和插入操作拆分，比把子查询嵌套在SELECT中可读性更高，适合复杂的数据清洗、转换后插入的场景。

相关注意事项 #

• 字段匹配要求：CTE返回的字段数量、顺序、数据类型必须和目标表的插入字段完全匹配；如果目标表存在非空、唯一键等约束，CTE的数据也要满足约束规则，否则会触发插入失败。如果需要自定义插入字段的顺序，可以显式声明插入字段，写法为：INSERT INTO "TEST_1"."PUBLIC"."EMP1" (emp_name,emp_id) SELECT ...，降低字段错位的概率。
• 权限要求：执行SQL的账号需要同时拥有源表的SELECT权限，以及目标表的INSERT权限，涉及的数据库、模式也需要分配对应USAGE权限。
• CTE生命周期：CTE属于单次SQL生效的临时结果集，当前SQL执行完成后就会自动释放，无法在其他SQL中复用该CTE。
• 事务特性：Snowflake默认开启DML自动提交，插入过程中如果出现异常，整个操作会自动回滚，不会产生部分数据写入的问题；如果需要手动控制事务，可以显式执行BEGIN TRANSACTION开启事务，验证插入结果无误后再执行COMMIT提交。
• 大小写敏感规则：示例中用双引号包裹的库、模式、表名是大小写敏感的，如果创建对象时没有用双引号强制指定大小写，Snowflake默认会将对象名转为大写存储，使用时可以省略双引号，避免大小写不匹配导致的对象不存在报错。
• 大数据量插入优化：如果CTE返回的数据集量级较大，可以提前确认目标表的集群键、分区配置是否合理，避免插入后产生过多小分片，影响后续的查询性能。

参考SQL代码 #

with 
ct2 (emp_name,emp_id) as (
  select emp_name,emp_id  
  from "TEST_1"."PUBLIC"."TEST11"
)
insert into "TEST_1"."PUBLIC"."EMP1"  
select emp_name,emp_id 
from ct2;

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