如何分批将Oracle中数百万条数据加载至SQL Server？

原代码的问题在于一次性将所有Oracle数据加载到DataTable中，内存占用过高容易溢出，而且即便设置了SqlBulkCopy的BatchSize，也只是在全量数据加载完成后才分批写入，无法实现边读边写的流式处理。

下面是实现分批读取+分批写入的PowerShell示例，核心是用OracleDataReader流式读取数据，配合SqlBulkCopy直接从Reader读取并分批提交，避免内存堆积：

# Oracle连接字符串
$oracleConnStr = "User Id=USERNAME;Password=PWD;Data Source=SERVICENAME"
# SQL Server连接字符串
$sqlConnStr = "server=SERVERNAME;database=DBNAME;trusted_connection=true"

# 源查询（建议添加排序，确保分页读取的一致性，避免重复或遗漏）
$sourceQuery = @"
SELECT * FROM SCHEMA1.SOURCE_TABLE_ORACLE
ORDER BY ID -- 替换为你的主键或唯一排序字段
"@

# 每次从Oracle读取的行数（可根据内存情况调整）
$fetchSize = 1000
# 每次提交到SQL Server的批次大小
$batchSize = 1000

# 初始化Oracle连接和命令
$oracleConn = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($oracleConnStr)
$oracleCmd = $oracleConn.CreateCommand()
$oracleCmd.CommandText = $sourceQuery
# 设置FetchSize，控制每次从Oracle数据库获取的行数，减少内存占用
$oracleCmd.FetchSize = $fetchSize * ($oracleCmd.GetColumnSchema() | ForEach-Object { $_.ColumnSize } | Measure-Object -Sum | Select-Object -ExpandProperty Sum)

try {
    $oracleConn.Open()
    # 流式读取数据，不会一次性加载所有数据到内存
    $reader = $oracleCmd.ExecuteReader([System.Data.CommandBehavior]::SequentialAccess)

    # 初始化SqlBulkCopy
    $sqlBulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($sqlConnStr)
    $sqlBulkCopy.DestinationTableName = "SCHEMA1.DESTINATION_TABLE_SQL"
    $sqlBulkCopy.BatchSize = $batchSize
    $sqlBulkCopy.NotifyAfter = $batchSize
    $sqlBulkCopy.EnableStreaming = $true
    $sqlBulkCopy.BulkCopyTimeout = 0

    # 绑定字段映射（如果源表和目标表字段名完全一致，可以省略这一步）
    foreach ($column in $reader.GetColumnSchema()) {
        $sqlBulkCopy.ColumnMappings.Add($column.ColumnName, $column.ColumnName) | Out-Null
    }

    # 注册进度通知事件（可选，用于查看批次提交情况）
    Register-ObjectEvent -InputObject $sqlBulkCopy -EventName SqlRowsCopied -Action {
        Write-Host "已成功提交 $($EventArgs.RowsCopied) 条数据"
    } | Out-Null

    # 边读边写，流式提交
    $sqlBulkCopy.WriteToServer($reader)

    Write-Host "数据迁移完成"
}
catch {
    Write-Error "迁移过程出错: $_"
}
finally {
    # 释放资源
    if ($reader -ne $null) { $reader.Close() }
    if ($oracleConn -ne $null) { $oracleConn.Close() }
    if ($sqlBulkCopy -ne $null) { $sqlBulkCopy.Close() }
}

关键要点说明 #

• 流式读取：使用OracleDataReader的SequentialAccess模式，逐行从Oracle获取数据，不会一次性加载全量数据到内存。
• FetchSize设置：控制每次从Oracle数据库拉取的行数，避免单次拉取过多数据占用内存。
• SqlBulkCopy流式写入：WriteToServer(Reader)方法直接从DataReader读取数据，配合BatchSize实现每批次提交指定数量的数据，边读边写，降低内存压力。
• 字段映射：如果源表和目标表字段名不一致，需要手动添加ColumnMappings确保字段对应正确。
• 进度通知：通过SqlRowsCopied事件可以实时查看批次提交情况，方便监控迁移进度。

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