There is a pair of identical databases with 80 tables each: source (Oracle) and target (SQL Server).

There is a Powershell 7.x script that processes the data: it reads a table from the source, does some simple processing in a local DataTable variable and writes results to the target. Rinse, repeat x 80.

The source is read using System.Data.OracleClient.OracleConnection and the target is populated with System.Data.SqlClient.SqlBulkCopy

79 of tables are relatively small and fit entirely in memory, so I am loading them using the System.Data.DataTable.Load() method.

1 table, however, is very large horizontally (each record contains a dozen of megabytes XML clob) and needs to be processed row-by-row i.e. a single row loaded from source, processed, written to target.

The question is: how can I loop through individual records from a System.Data.OracleClient.OracleConnection dataset, and pull them to a local DataTable object? I was looking at the System.Data.DataTable.LoadDataRow() method but it doesn't seem to be doing what I need here.

CodePudding user response：

Instead of loading a DataTable, just call ExecuteReader, and pass the DataReader to SqlBulkCopy.WriteToServer.

If you want to use LoadDataRow, still use ExecuteReader, and while rdr.Read() use rdr.GetValues to copy the row into an object array, which you can pass to LoadDataRow.