How to perform a Union All on 2 DataTables

Question

I am not using Entity Framework. There are 2 Datatables, each with different no. of columns. There is one common column - ItemId

If it was Union, I would have used 'Merge', but don't know how to do a Union All for these 2 DataTables

public static void Main()
    {
        //First DataTable
        DataTable dt1 = new DataTable();
        dt1.Columns.Add("ItemId");
        dt1.Columns.Add("Name");
        dt1.Columns.Add("Color");

        DataRow dr = dt1.NewRow();
        dr["ItemId"] = "1";
        dr["Name"] = "Name1";
        dr["Color"] = "Color1";
        dt1.Rows.Add(dr);

        dr = dt1.NewRow();
        dr["ItemId"] = "2";
        dr["Name"] = "Name2";
        dr["Color"] = "Color2";
        dt1.Rows.Add(dr);

        //Second DataTable
        DataTable dt2 = new DataTable();
        dt2.Columns.Add("ItemId");
        dt2.Columns.Add("Name");
        dt2.Columns.Add("Price");

        DataRow dr2 = dt2.NewRow();
        dr2["ItemId"] = "1";
        dr2["Name"] = "Name1";
        dr2["Price"] = "100";
        dt2.Rows.Add(dr2);

        dr2 = dt2.NewRow();
        dr2["ItemId"] = "2";
        dr2["Name"] = "Name3";
        dr2["Price"] = "200";
        dt2.Rows.Add(dr2);
    }

Expected Output

ItemId  Name    Color   Price
1       Name1   Color1
2       Name2   Color2
1       Name1            100  
2       Name3            200

I have no idea how to interpret "There is one common column - ItemId" since there seem to be two columns with identical names (`ItemId` and `Name`) and they seem to be being treated identically in your expected output. — Damien_The_Unbeliever, Dec 31 '15 at 09:03
I am bit worries about the result you want as it having duplicate ItemId. Does this behaviour is correct? — Kunal Kakkad, Dec 31 '15 at 09:09
Possible duplicate of [Combining n DataTables into a Single DataTable](http://stackoverflow.com/questions/12278978/combining-n-datatables-into-a-single-datatable) — Kunal Kakkad, Dec 31 '15 at 09:50
@KunalKakkad. Yes that behaviour is correct. The ItemIds may be same in the 2 datatables — sukesh, Dec 31 '15 at 10:08
@Damien_The_Unbeliever. That was a pseudo example. In the real code, one column ItemId for sure is common. The others may or may not be same. — sukesh, Dec 31 '15 at 10:08
So, you have a *small* example consisting of four rows and columns of data and you still aren't able to make it a close match to your actual requirements? What is the *relevance* of `ItemId` being common since it appears to receive no special treatment in your example? — Damien_The_Unbeliever, Dec 31 '15 at 10:12

score 4 · Answer 1 · answered Dec 31 '15 at 09:41

4

You can use DataTable.Merge()

If you have primary key in both the table then it will perform merge on primary key else it will directly append all the records.

In youe case make ItemID as primary key.

answered Dec 31 '15 at 09:41

Munavvar Husein Shaikh

579
6
22

Merge will remove duplicates. Doesn't it? – sukesh Dec 31 '15 at 10:09
No merge will not delete a duplicate record, It will just overwrite a record source record with target record based on primary key. – Munavvar Husein Shaikh Dec 31 '15 at 10:36
If you want to just append 2nd data table rows then don't specify ItemID as primary key, Just use merge. – Munavvar Husein Shaikh Dec 31 '15 at 10:39

Владимир В · Answer 2 · 2015-12-31T09:34:57.397

1

using System.Linq.Expressions; ......

    var result1 = from row1 in dt1.AsEnumerable()                     
                 select new { Name = row1.Field<String>("Name"), Color = row1.Field<String>("Color"), Price = "" };

        var result2 = from row1 in dt2.AsEnumerable()                     
                      select new { Name = row1.Field<String>("Name"), Color = "", Price = row1.Field<String>("Price") };
        var res = result1.Concat(result2);

        foreach (var item in res)
        Console.WriteLine("{0} - {1} - {2}", item.Name, item.Color, item.Price);

edited Dec 31 '15 at 09:34

answered Dec 31 '15 at 09:29

Владимир В

159
3
10

What if, I do not know the names of the columns before hand – sukesh Dec 31 '15 at 10:19

score 0 · Answer 3 · answered Sep 12 '19 at 13:05

Even though this question is years old, for anyone looking for another way to do this:

        public static DataTable MergeTables(DataTable dt1, DataTable dt2)
    {
        DataTable dt3 = dt1.Clone();
        foreach (DataColumn col in dt2.Columns)
        {
            string strColumnName = col.ColumnName;
            int intColNum = 1;
            while (dt3.Columns.Contains(strColumnName))
            {
                strColumnName = string.Format("{0}_{1}", col.ColumnName, ++intColNum);
            }
            dt3.Columns.Add(strColumnName, col.DataType);
        }
        var Mergered = dt1.AsEnumerable().Zip(dt2.AsEnumerable(), (r1, r2) => r1.ItemArray.Concat(r2.ItemArray).ToArray());
        foreach (object[] rowFields in Mergered)
            dt3.Rows.Add(rowFields);

        return dt3;
    }

How to perform a Union All on 2 DataTables

3 Answers3