How to append one DataTable to another DataTable

Question

I would like to append one DataTable to another DataTable. I see the DataTable class has two methods; "Load(IDataReader)" and "Merge(DataTable)". From the documentation, both appear to 'merge' the incoming data with the existing DataTable if rows exist. I will be doing the merge in a data access layer.

I could use an IDataReader and use the Load method to merge the DataTables. Or I could load a DataSet using the IDataReader, get the DataTable from the DataSet, and then use the Merge method to merge the DataTables.

I was wondering if someone could tell me which is the proper method to use?

Alternatively, let me know if you have a different suggestion on how to accomplish this task.

Answer 1

Merge takes a DataTable, Load requires an IDataReader - so depending on what your data layer gives you access to, use the required method. My understanding is that Load will internally call Merge, but not 100% sure about that.

If you have two DataTables, use Merge.

Answer 2

The datatype in the same columns name must be equals.

dataTable1.Merge(dataTable2);

After that the result is:

dataTable1 = dataTable1 + dataTable2

Answer 3

You could let your DataAdapter do the work. DataAdapter.Fill(DataTable) will append your new rows to any existing rows in DataTable.

Answer 4

Add two datasets containing datatables, now it will merge as required

DataSet ds1 = new DataSet();
DataSet ds2 = new DataSet();

DataTable dt1 = new DataTable();
dt1.Columns.Add(new DataColumn("Column1", typeof(System.String)));

DataRow newSelRow1 = dt1.NewRow();
newSelRow1["Column1"] = "Select";
dt1.Rows.Add(newSelRow1);

DataTable dt2 = new DataTable();
dt2.Columns.Add(new DataColumn("Column1", typeof(System.String)));

DataRow newSelRow2 = dt1.NewRow();
newSelRow2["Column1"] = "DataRow1Data";  // Data
dt2.Rows.Add(newSelRow2);

ds1.Tables.Add(dt1);
ds2.Tables.Add(dt2);

ds1.Tables[0].Merge(ds2.Tables[0]);

Now ds1 will have the merged data

Answer 5

Consider a solution that will neatly handle arbitrarily many tables.

//ASSUMPTION: All tables must have the same columns
var tables = new List<DataTable>();
tables.Add(oneTableToRuleThemAll);
tables.Add(oneTableToFindThem);
tables.Add(oneTableToBringThemAll);
tables.Add(andInTheDarknessBindThem);
//Or in the real world, you might be getting a collection of tables from some abstracted data source.

//behold, a table too great and terrible to imagine
var theOneTable = tables.SelectMany(dt => dt.AsEnumerable()).CopyToDataTable();

Encapsulated into a helper for future reuse:

public static DataTable CombineDataTables(params DataTable[] args)
{
    return args.SelectMany(dt => dt.AsEnumerable()).CopyToDataTable();
}

Just have a few tables declared in code?

var combined = CombineDataTables(dt1,dt2,dt3);

Want to combine into one of the existing tables instead of creating a new one?

dt1 = CombineDataTables(dt1,dt2,dt3);

Already have a collection of tables, instead of declared one by one?

//Pretend variable tables already exists
var tables = new[] { dt1, dt2, dt3 };
var combined = CombineDataTables(tables);

Answer 6

use loop

  for (int i = 0; i < dt1.Rows.Count; i++)
        {      
           dt2.Rows.Add(dt1.Rows[i][0], dt1.Rows[i][1], ...);//you have to insert all Columns...
        }