How to read a database record with a DataReader and add it to a DataTable

I have some data in a Oracle database table(around 4 million records) which i want to transform and store in a MSSQL database using ADO.NET. So far i used (for much smaller tables) a DataAdapter to read the data out of the Oracle DataBase and add the DataTable to a DataSet for further processing.

When i tried this with my huge table, there was a outofmemory exception thrown. ( I assume this is because i cannot load the whole table into my memory) :)

Now i am looking for a good way to perform this extract/transfer/load, without storing the whole table in the memory. I would like to use a DataReader and read the single dataRecords in a DataTable. If there are about 100k rows in it, I would like to process them and clear the DataTable afterwards(to have free memory again).

Now i would like to know how to add a single datarecord as a row to a dataTable with ado.net and how to completly clear the dataTable out of memory: My code so far:

Dim dt As New DataTable

    Dim count As Int32
    count = 0
    ' reads data records from or开发者_C百科acle database table'
    While rdr.Read()

        'read n records and add them to a dataTable'
        While count < 10000
            dt.Rows.Add(????)

            count = count + 1
        End While

        'transform data in the dataTable, and insert it to the destination'


        ' flush the dataTable after insertion'
        count = 0
    End While

Thank you very much for your response!

You could use your original method, but do it in batches using limit and skip in the select statement. So you would just do 100,000 rows at a time and loop until you get all the data.

That way you wouldn't have to change your code too much