CODEWITHSUNDEEP
c#
Vishavajeet
Vishavajeet

Reputation: 1

Merge two datatables into a single one in C#

I have two datatables, first has one column No Of Specification and the second has column NoOfSpec, but I want to merge under single column as No Of Specification.

Data table #1 

No Of Specification       
--------------------      
1                            
2                            
3

Data table #2

NoOfSpec
---------------------
4
5
6

and the result should be

No Of Specification
----------------------
1
2
3
4
5
6

Note: there are multiple columns in both the tables

Upvotes: 0

Views: 592

Answers (2)

steve16351
steve16351

Reputation: 5812

Here is one approach you could take to join the data using Linq. The Union of the data rows in the two tables are taken, and an anonymous object is created for each in which you set the key for the merge - in your case, No of Specification for the first table and NoOfSpec from the second. Then, we use a GroupBy on that union to get unique values for the merge. Finally, we select the first occurrence of each unique key from the union and copy to a new DataTable.

DataTable firstTable = new DataTable();
firstTable.Columns.Add("No of Specification", typeof(int));
firstTable.Columns.Add("OtherCol", typeof(string));
firstTable.Rows.Add(1, "A");
firstTable.Rows.Add(2, "B");
firstTable.Rows.Add(3, "C");

DataTable secondTable = new DataTable();
secondTable.Columns.Add("NoOfSpec", typeof(int));
secondTable.Columns.Add("OtherCol", typeof(string));
secondTable.Rows.Add(3, "F");
secondTable.Rows.Add(4, "G");
secondTable.Rows.Add(5, "H");

var mergedTable = firstTable.AsEnumerable().Select(dataRow => new { Key = dataRow["No of Specification"], Row = dataRow })
    .Union(secondTable.AsEnumerable().Select(dataRow => new { Key = dataRow["NoOfSpec"], Row = dataRow }))
    .GroupBy(a => a.Key, a => a.Row)
    .Select(a => a.First())
    .CopyToDataTable();

Output:

+----------+----------+
| NoOfSpec | OtherCol |
+----------+----------+
| 1        | A        |
| 2        | B        |
| 3        | C        |
| 4        | G        |
| 5        | H        |
+----------+----------+

Upvotes: 1

atiqorin
atiqorin

Reputation: 466

SQL is following. I am assuming other columns are same. The other column I gave is named [value] which exists in both tables. For more columns you have to add them one by one as two columns of different names are being merged.

SELECT L.[No Of Specification] as [No Of Specification], L.[value]
FROM [Data Table 1] L
UNION --if you know there can not be duplicates
SELECT R.[NoOfSpec], R.[value]
FROM [Data Table 2] R
ORDER BY [No Of Specification]

Upvotes: 0

Related Questions