How to append two DataTable column wise in C#

Question

I have one DataTable T1 with M Columns and another DataTable T2 with N columns and they have equal number of rows.

I want to combine T1 and T2 into another DataTable T so that the resulting table T contains M+N number of Columns.

Is there any easy way to it in C#, I mean without using Loop.

So going from 5 to 6 rows, because T2 contains more columns or ? — Jack Andersen, Jan 15 '16 at 12:12
Both table has same number of rows but may or may not have same number of columns. I just want to combine two tables column wise.Resulting Table will have same number of rows as T1(or T2). — Saikat, Jan 15 '16 at 12:16
May this helps you: http://stackoverflow.com/questions/2379747/create-combined-datatable-from-two-datatables-joined-with-linq-c-sharp — anmarti, Jan 15 '16 at 12:45
If your tables have the same number of rows but do not share a column name, how would you decide which row in T1 should be combined with a row in T2? Does it matter at all? — Sándor Mátyás Márton, Jan 15 '16 at 12:45

score 1 · Answer 1 · answered Jan 15 '16 at 14:04

You can use LINQ outer join query and get the data from both table to your final table. A sample code below

       //First DataTable
        DataTable dt = new DataTable();
        DataColumn dc = new DataColumn("ID");
        DataColumn dc1 = new DataColumn("Name");

        dt.Columns.AddRange(new DataColumn[] {dc, dc1 });

        DataRow dr = dt.NewRow();
        dr[dc] = "1";
        dr[dc1] = "Test";
        dt.Rows.Add(dr);

        //Second DataTable
        DataTable dt1 = new DataTable();
        DataColumn dc2 = new DataColumn("ID");
        DataColumn dc3 = new DataColumn("City");

        dt1.Columns.AddRange(new DataColumn[] { dc2, dc3 });

        DataRow dr1 = dt1.NewRow();
        dr1[dc2] = "1";
        dr1[dc3] = "Belgium";
        dt1.Rows.Add(dr1);


        //Rasult DataTable
        DataTable result = new DataTable();
        DataColumn col1 = new DataColumn("ID");
        DataColumn col2 = new DataColumn("Name");
        DataColumn col3 = new DataColumn("City");
        result.Columns.AddRange(new DataColumn[] { col1, col2, col3 });


        // Join both table data
        var data = from row1 in dt.AsEnumerable()
                   join row2 in dt1.AsEnumerable()
                   on row1.Field<string>("ID") equals row2.Field<string>("ID") into test
                   from rw in test.DefaultIfEmpty()
                   select result.LoadDataRow(new object[]
                    {
                         row1.Field<string>("ID"),
                         row1.Field<string>("Name"),
                         rw == null ? "No City" : rw.Field<string>("City")
                    }, false);

        data.CopyToDataTable();

Nice answer Rahul. But creation of columns of result datatable is static. — Saikat, Jan 18 '16 at 06:58
@Saikat, that has to be else there is not other way to get the combined schema of both DT's — Rahul, Jan 18 '16 at 10:49

Mihai-Daniel Virna · Answer 2 · 2016-01-15T14:55:30.307

There isn't any way apart from the loop. There is a merge function for DataTables but it is doing the operation row wise, even thought you can specify to add columns if needed, it will leave the other columns null and you will get something like this:

values values values null null 
null   null   null   values values

With this approach you can then loop through all the rows and have a step of 2 in which you will merge two rows into one in a new DataTable, but for this you need one more DataTable to store the result.

If you know you have the same number of rows you can add columns to the table that has the smaller number of columns and then copy them across.

Again, I would prefer the loop.

How to append two DataTable column wise in C#

2 Answers2