-1

How can I copy rows from one DataTable to another? Using DataTable.Copy() is not an option since this creates a NEW DataTable and deletes any existing DataRows. What I'm trying to do is add rows in a List<DataTable> to another table. Is there some method available that is able to do this? Or is iterating through all tables and rows and calling DataTable.ImportRow() my only option?

[UPDATE] Ok, this is the full scenario. I have a DataTable with 2 million+ records. Loading it throws System.OutOfMemoryException. So, I decided I need to page/batch load it. I'm creating the destination table using DataAdapter.FillSchema() for the purpose of loading only the columns. After that, I get the 'full records' using DataAdapter.Fill() (another DataTable), then split it into a List<DataTable>. When using DataTable.Merge, this exception is thrown: "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

  • Have you looked at DataTable.Merge or DataTable.Load? – Michael Liu Jun 18 '15 at 03:36
  • DataTable.Load is not what I'm looking for since what I need to pass is a DataTable object and not IDataReader. On the other hand, when i use DataTable.Merge, an error 'Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.' is thrown. Additional note: the destination DataTable is created using DataAdapter.FillSchema. That's why I don't understand why it would throw an error like this. –  Jun 18 '15 at 03:53
  • As far as i know ImportRow is the only option. http://stackoverflow.com/questions/12025012/c-sharp-simple-way-to-copy-or-clone-a-datarow http://www.aspdotnet-suresh.com/2013/08/copy-add-rows-from-one-datatable-to-another-datatable.html – shanmugharaj Jun 18 '15 at 04:02

1 Answers1

1

I tried merge as Michael said with the following code and its working for me.

DataTable table = new DataTable();
table.Columns.Add("Name");
table.Columns.Add("Address");

table.Rows.Add("Shan", "ADC Street");

DataTable table2 = new DataTable();
table2.Columns.Add("Name");
table2.Columns.Add("Address");
table2.Rows.Add("Raj", "EFG Street");

table2.Merge(table,true);

Console.WriteLine(table2.Rows.Count);      //2
Console.WriteLine(table2.Rows[0]["Name"]); //Raj
Console.WriteLine(table2.Rows[1]["Name"]); //Shan
shanmugharaj
  • 3,814
  • 7
  • 42
  • 70