如何将一个Synapse实例中的所有表复制到另一个实例？

针对Synapse实例间全表复制的需求，给你几个高效的解决办法，避免手动逐个配置Copy Activity：

1. 元数据驱动的Synapse管道批量复制 #

这是Synapse原生的主流方案，利用管道的Lookup+ForEach实现自动化遍历：

• 步骤1：获取源表元数据
  添加一个Lookup活动，连接源Synapse的SQL池/数据库，执行查询：

  SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
  WHERE TABLE_TYPE = 'BASE TABLE' -- 仅获取物理表，排除视图
  

• 步骤2：遍历表执行复制
  添加ForEach活动，将Items设为@activity('Lookup Tables').output.value（引用Lookup的结果），在循环内部配置Copy Activity：
  • 源数据集的表名动态设置为：@item().TABLE_SCHEMA + '.' + @item().TABLE_NAME
  • 目标数据集的表名同理，确保目标库对应的schema已存在（也可在管道中添加自动创建schema的逻辑）
• 优点：可视化配置，一次设置后可重复运行，还能扩展增量复制逻辑

2. PowerShell脚本自动化复制 #

适合需要批量执行或集成到CI/CD流程的场景：

• 先安装Az.Synapse模块，再用脚本枚举源表并触发复制：

  # 连接Azure账户
  Connect-AzAccount
  
  # 定义源和目标实例信息
  $sourceWorkspace = "source-synapse-workspace"
  $sourceSqlPool = "source-sql-pool"
  $sourceDb = "source-db"
  $targetWorkspace = "target-synapse-workspace"
  $targetSqlPool = "target-sql-pool"
  $targetDb = "target-db"
  
  # 获取源表全名称列表
  $tables = Invoke-SynapseSqlCommand -WorkspaceName $sourceWorkspace -SqlPoolName $sourceSqlPool -DatabaseName $sourceDb -Query "SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS FullTableName FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'"
  
  # 循环用CTAS复制每个表（需先在目标实例创建指向源的链接服务）
  foreach ($table in $tables) {
      $tableName = $table.FullTableName
      $ctasQuery = "CREATE TABLE $tableName AS SELECT * FROM [source-linked-service].[$tableName]"
      Invoke-SynapseSqlCommand -WorkspaceName $targetWorkspace -SqlPoolName $targetSqlPool -DatabaseName $targetDb -Query $ctasQuery
  }
  

• 注意：需先在目标实例创建指向源实例的链接服务，并确保操作账号权限足够。

3. CTAS批量复制（适用于Dedicated SQL Pool） #

如果两个实例都是Dedicated SQL Pool，用CREATE TABLE AS SELECT (CTAS)的性能更高：

• 先在目标池创建源池的链接服务，再用查询批量生成CTAS执行脚本：

  SELECT 
      "CREATE TABLE [" + TABLE_SCHEMA + "].[" + TABLE_NAME + "] AS SELECT * FROM [source-linked-service].[" + TABLE_SCHEMA + "].[" + TABLE_NAME + "];"
  FROM INFORMATION_SCHEMA.TABLES 
  WHERE TABLE_TYPE = 'BASE TABLE'
  

  将生成的脚本复制到目标Synapse的查询窗口执行即可。

关键注意事项 #

• 对象依赖：以上方法仅复制表结构和数据，索引、外键、触发器等需要单独迁移，可通过sys.indexes、sys.foreign_keys等系统视图生成对应脚本。
• 性能优化：大表复制建议启用PolyBase，或分批次复制避免资源占用过高。
• 权限验证：确保操作账号拥有源实例的SELECT权限和目标实例的CREATE TABLE、INSERT权限。

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