批量从Oracle阅读数百万行并加载到SQL Server

hl0ma9xz  于 2023-06-22  发布在  Oracle
关注(0)|答案(1)|浏览(139)

你能给我发一些例子吗?如何从Oracle批量加载数百万行到SQL Server(例如:在阅读并加载1000行后提交)?
我试图通过使用Oracle.ManagedDataAccess.Client.OracleDataAdapter从Oracle读取数据,但由于内存限制,使用Fill方法失败。
我desparately试图添加属性,如EnableStreaming和BatchSize,但数据不加载连续分批到目标表,但只有在读取过程结束时(在情况下,它不会在阅读过程中的内存失败)
这是我使用的代码:

$oracleConnectionString = "User Id=USERNAME;Password=PWD;Data Source=SERVICENAME";
$sourceQuery = "select * from SCHEMA1.SOURCE_TABLE_ORACLE where rownum < 50000000";
$adapter = new-object Oracle.ManagedDataAccess.Client.OracleDataAdapter($sourceQuery, $oracleConnectionString );
$dtbl = new-object System.Data.DataTable
$adapter.Fill($dtbl);
$sqlConnectionString = "server=SERVERNAME;database=DBNAME;trusted_connection=true";
$sqlbc = new-object system.data.sqlclient.Sqlbulkcopy($sqlConnectionString );
$sqlbc.DestinationTableName="SCHEMA1.DESTINATION_TABLE_SQL";
$sqlbc.BatchSize=1000
$sqlbc.NotifyAfter = 1000
$sqlbc.EnableStreaming = $true
$sqlbc.BulkCopyTimeout = 0
$sqlbc.WriteToServer($dtbl);
2hh7jdfx

2hh7jdfx1#

你不需要适配器。只需直接使用命令,打开读取器,并将该读取器直接传递给WriteToServer

$oracleConnectionString = "User Id=USERNAME;Password=PWD;Data Source=SERVICENAME";
$sqlConnectionString = "server=SERVERNAME;database=DBNAME;trusted_connection=true";
$sourceQuery = "select * from SCHEMA1.SOURCE_TABLE_ORACLE where rownum < 50000000";

try
{
    $oracleConn = [Oracle.ManagedDataAccess.Client.OracleConnection]::new($oracleConnectionString);
    $oracleComm = [Oracle.ManagedDataAccess.Client.OracleCommand]::new($sourceQuery, $oracleConn);
    $sqlbc = [System.Data.SqlClient.SqlBulkCopy]::new($sqlConnectionString);
    $sqlbc.DestinationTableName = "SCHEMA1.DESTINATION_TABLE_SQL";
    $sqlbc.BatchSize = 1000;
    $sqlbc.NotifyAfter = 1000;
    $sqlbc.EnableStreaming = $true;
    $sqlbc.BulkCopyTimeout = 0;

    $oracleConn.Open();
    $reader = $oracleComm.ExecuteReader();
    $sqlbc.WriteToServer($reader);
}
finally
{
    if ($sqlbc -ne $null) { $sqlbc.Dispose(); }
    if ($reader -ne $null) { $reader.Dispose(); }
    if ($oracleComm -ne $null) { $oracleComm.Dispose(); }
    if ($oracleConn -ne $null) { $oracleConn.Dispose(); }
}

相关问题