如何使用PowerShell实现跨域未链接SQL Server间的全库数据迁移

你原有代码中导出CSV的2行代码，可替换为目标表删除+自动建表+批量插入的逻辑实现需求，核心使用.NET内置的SqlBulkCopy类完成高效数据写入，无需生成中间文件。 #

完整替换代码段 #

将原来的2行导出代码替换为以下内容：

# 取源表数据
$sourceTable = $wanteddata_src.Tables[0]
# 拆分目标表的schema和表名
$schemaName, $tableName = $dst_name -split '\.', 2

$connection_dst.Open()
try {
    # 1. 删除已存在的目标同名表
    $dropSql = "IF OBJECT_ID('$dst_name', 'U') IS NOT NULL DROP TABLE $dst_name"
    $dropCmd = New-Object System.Data.SqlClient.SqlCommand($dropSql, $connection_dst)
    $dropCmd.ExecuteNonQuery() | Out-Null

    # 2. 自动生成建表语句（适配常见数据类型，特殊类型可自行扩展）
    $columnDefs = @()
    foreach ($col in $sourceTable.Columns) {
        $sqlDataType = switch ($col.DataType.Name) {
            'Int16'     { 'SMALLINT' }
            'Int32'     { 'INT' }
            'Int64'     { 'BIGINT' }
            'String'    { 'NVARCHAR(MAX)' }
            'DateTime'  { 'DATETIME' }
            'Boolean'   { 'BIT' }
            'Decimal'   { 'DECIMAL(18,6)' }
            'Double'    { 'FLOAT' }
            'Guid'      { 'UNIQUEIDENTIFIER' }
            'Byte[]'    { 'VARBINARY(MAX)' }
            default     { 'NVARCHAR(MAX)' }
        }
        $nullFlag = if ($col.AllowDBNull) { 'NULL' } else { 'NOT NULL' }
        $columnDefs += "[$($col.ColumnName)] $sqlDataType $nullFlag"
    }
    $createSql = "CREATE TABLE $dst_name ($($columnDefs -join ', '))"
    $createCmd = New-Object System.Data.SqlClient.SqlCommand($createSql, $connection_dst)
    $createCmd.ExecuteNonQuery() | Out-Null

    # 3. 批量写入数据到目标表
    $bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($connection_dst)
    $bulkCopy.DestinationTableName = $dst_name
    # 列名映射，避免列顺序不一致导致写入错误
    foreach ($col in $sourceTable.Columns) {
        $bulkCopy.ColumnMappings.Add($col.ColumnName, $col.ColumnName) | Out-Null
    }
    # 大数据量可设置批量提交大小，按需调整
    # $bulkCopy.BatchSize = 10000
    $bulkCopy.WriteToServer($sourceTable)
    $bulkCopy.Dispose()
}
finally {
    $connection_dst.Close()
}

可选优化说明 #

• 若需要保留源表的主键、自增属性、索引等结构，可调整获取表结构的逻辑，直接从源库sys.columns、sys.indexes等系统表读取完整字段属性生成建表语句
• 源表存在自增列需要保留原值时，初始化SqlBulkCopy时添加KeepIdentity配置：

  $bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($connection_dst, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity, $null)
  

• 数据量超过10万行时建议设置BatchSize参数，避免单次写入内存占用过高

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