Snowflake中如何跨库动态加载表并为表名追加指定字符

核心逻辑是批量遍历源库指定Schema下的所有表，自动完成结构复制、全量数据写入，目标表统一追加de_前缀，不需要手动逐个处理单表。

前置权限要求 #

• 执行账号持有源库、源Schema的USAGE权限，以及源表的SELECT权限
• 执行账号持有目标库、目标Schema的USAGE、CREATE TABLE权限
• 提前确认目标库下没有de_+源表名的重名表，避免建表报错；如果需要覆盖旧表，可以在逻辑里加删表语句，注意提前备份数据。

方案1：存储过程批量动态同步（全库同步推荐） #

写一次存储过程即可自动完成所有表的复制，支持自定义过滤规则、自定义前缀，代码如下：

-- 先配置同步参数，替换成你实际的环境信息
SET source_db = '你的源数据库名';
SET source_schema = '源表所在Schema，一般是PUBLIC';
SET target_db = '你的目标数据库名';
SET target_schema = '目标表存放Schema';
SET table_prefix = 'de_'; -- 要加的表前缀，可自行修改

CREATE OR REPLACE PROCEDURE sp_batch_copy_tables()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    -- 游标查询源Schema下所有普通用户表
    cur CURSOR FOR 
        SELECT TABLE_NAME 
        FROM TABLE(INFORMATION_SCHEMA.TABLES)
        WHERE TABLE_CATALOG = $source_db
          AND TABLE_SCHEMA = $source_schema
          AND TABLE_TYPE = 'BASE TABLE';
    v_src_table VARCHAR;
    v_tgt_table VARCHAR;
    v_exec_log VARCHAR DEFAULT '';
BEGIN
    FOR rec IN cur DO
        v_src_table := $source_db || '.' || $source_schema || '.' || rec.TABLE_NAME;
        v_tgt_table := $target_db || '.' || $target_schema || '.' || $table_prefix || rec.TABLE_NAME;
        
        -- 复制完整表结构
        EXECUTE IMMEDIATE 'CREATE TABLE ' || v_tgt_table || ' LIKE ' || v_src_table;
        -- 写入全量数据
        EXECUTE IMMEDIATE 'INSERT INTO ' || v_tgt_table || ' SELECT * FROM ' || v_src_table;
        
        v_exec_log := v_exec_log || '复制完成：' || rec.TABLE_NAME || ' -> ' || $table_prefix || rec.TABLE_NAME || '\n';
    END FOR;
    RETURN v_exec_log;
END;
$$;

-- 执行存储过程开始同步
CALL sp_batch_copy_tables();

几个实用调整点：

• 需要覆盖目标库已有同名表的话，在建表语句前加一行EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS ' || v_tgt_table;即可
• 只需要同步部分表的话，在游标查询的WHERE条件里加过滤规则，比如AND TABLE_NAME LIKE 'order_%'就只会同步表名以order_开头的表
• 需要做增量同步的话，把INSERT语句的SELECT部分加对应时间过滤条件就行，比如SELECT * FROM ' || v_src_table || ' WHERE update_time >= DATEADD(day, -1, CURRENT_DATE())就是同步最近1天的增量数据

注意这里用LIKE语法复制表结构而不是CREATE TABLE AS SELECT (CTAS)，是因为CTAS会丢失源表的字段非空约束、默认值、聚类键、列注释等属性，LIKE会1:1复刻完整表结构，不会有属性缺失的问题。

方案2：单表手动复制（少量表临时场景） #

如果只需要复制1-2张表，不用写存储过程，直接跑单条SQL即可：

-- 示例：复制源库的user_info表到目标库，目标表名de_user_info
CREATE TABLE 目标库名.PUBLIC.de_user_info LIKE 源库名.PUBLIC.user_info;
INSERT INTO 目标库名.PUBLIC.de_user_info SELECT * FROM 源库名.PUBLIC.user_info;

常见报错处理 #

• 权限不足报错：给执行角色补对应权限即可，参考语句：

  GRANT USAGE ON DATABASE 源库名 TO ROLE 你的执行角色名;
  GRANT SELECT ON ALL TABLES IN SCHEMA 源库名.PUBLIC TO ROLE 你的执行角色名;
  GRANT CREATE TABLE ON SCHEMA 目标库名.PUBLIC TO ROLE 你的执行角色名;
  

• 字段不匹配报错：大概率是目标库已经存在同名的de_开头表，结构和源表不一致，删掉旧表重跑即可
• 大表同步慢：可以在建表时加上COPY GRANTS参数继承源表权限，超大表可以结合Snowflake的零拷贝克隆功能做初始同步，性能比普通INSERT高几个量级。

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