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

Question

Hello

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 oracle 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!

Answer 1

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