Combining Two Tables Into One

I need to merge two data schema into one. I have Schema1 and Schema2. I need to join these two into Schema3. Furthermore, I have a Select statement which queries a data set but I c

Solution 1:

You could use this extension method here which i've written from scratch recently for another question. It enables to merge multiple tables by a common key. If no key is specified it will just use the default DataTable.Merge method:

publicstatic DataTable MergeAll(this IList<DataTable> tables, String primaryKeyColumn)
{
    if (!tables.Any())
        thrownew ArgumentException("Tables must not be empty", "tables");
    if(primaryKeyColumn != null)
        foreach(DataTable t in tables)
            if(!t.Columns.Contains(primaryKeyColumn))
                thrownew ArgumentException("All tables must have the specified primarykey column " + primaryKeyColumn, "primaryKeyColumn");

    if(tables.Count == 1)
        return tables[0];

    DataTable table = new DataTable("TblUnion");
    table.BeginLoadData(); // Turns off notifications, index maintenance, and constraints while loading dataforeach (DataTable t in tables)
    {
        table.Merge(t); // same as table.Merge(t, false, MissingSchemaAction.Add);
    }
    table.EndLoadData();

    if (primaryKeyColumn != null)
    {
        // since we might have no real primary keys defined, the rows now might have repeating fields// so now we're going to "join" these rows ...var pkGroups = table.AsEnumerable()
            .GroupBy(r => r[primaryKeyColumn]);
        var dupGroups = pkGroups.Where(g => g.Count() > 1);
        foreach (var grpDup in dupGroups)
        { 
            // use first row and modify it
            DataRow firstRow = grpDup.First();
            foreach (DataColumn c in table.Columns)
            {
                if (firstRow.IsNull(c))
                {
                    DataRow firstNotNullRow = grpDup.Skip(1).FirstOrDefault(r => !r.IsNull(c));
                    if (firstNotNullRow != null)
                        firstRow[c] = firstNotNullRow[c];
                }
            }
            // remove all but first rowvar rowsToRemove = grpDup.Skip(1);
            foreach(DataRow rowToRemove in rowsToRemove)
                table.Rows.Remove(rowToRemove);
        }
    }

    return table;
}

You can call it in this way:

var tables= new[] { Schema1, Schema2};
DataTableSchema3= tables.MergeAll("ID");

Edit: If you don't need a new DataTable with the merged schema you could also use Linq-To-DataSet (now VB.NET):

Dim schema3 = From r1 In schema1
          Join r2 In schema2 On r1.Field(Of Int32)("ID") Equals r2.Field(Of Int32)("ID")
          Select New With {
                .ID = r1.Field(Of Int32)("ID"),
                .Food = r1.Field(Of String)("Food"),
                .Book = r1.Field(Of String)("Book"),
                .Rice = r1.Field(Of String)("Rice"),
                .Cave = r1.Field(Of String)("Cave"),
                .Carpet = r2.Field(Of String)("Carpet"),
                .Strings = r2.Field(Of String)("Strings"),
                .Run = r2.Field(Of String)("Run")
            }

Solution 2:

Try this:

''' <summary>''' Merge two datatables that have a 1:1 relationship''' </summary>''' <param name="dtb1">Required Datatable.</param>''' <param name="dtb2">Required Datatable.</param>''' <param name="dtb1MatchField">Required String. Field name in dtb1 to use to match records</param>''' <param name="dtb2MatchField">Required String. Field name in dtb2 to use to match records</param>''' <remarks></remarks>'PrivateFunction MergeDataTables(ByVal dtb1 As DataTable, ByVal dtb2 As DataTable, ByVal dtb1MatchField AsString, ByVal dtb2MatchField AsString) As DataTable
    Dim dtbOutput As DataTable = dtb1.Copy
    Dim lstSkipFields AsNew List(OfString)
    ForEach dcl As DataColumn In dtb2.Columns
      Try
        dtbOutput.Columns.Add(dcl.ColumnName, dcl.DataType)
      Catch ex As DuplicateNameException
        lstSkipFields.Add(dcl.ColumnName)
      EndTryNext dcl
    'Merge dtb2 records that match existing records in dtb1'Dim dtb2Temp As DataTable = dtb2.Copy
    For int2 AsInteger = dtb2Temp.Rows.Count - 1To0Step -1Dim drw2 As DataRow = dtb2Temp.Rows(int2)
      Dim o2 AsObject = drw2(dtb2MatchField)
      ForEach drw1 As DataRow In dtbOutput.Rows
        Dim o1 AsObject = drw1(dtb1MatchField)
        If o1.ToString = o2.ToString ThenForEach dcl As DataColumn In dtb2Temp.Columns
            IfNot lstSkipFields.Contains(dcl.ColumnName) Then
              drw1(dcl.ColumnName) = drw2(dcl.ColumnName)
            EndIfNext dcl
          dtb2Temp.Rows.Remove(drw2)
        EndIfNext drw1
    Next int2
    'add rows that weren not in dtb1'ForEach drw2 As DataRow In dtb2Temp.Rows
      Dim drw1 As DataRow = dtbOutput.NewRow
      ForEach dcl As DataColumn In dtb2Temp.Columns
        drw1(dcl.ColumnName) = drw2(dcl.ColumnName)
      Next dcl
      dtbOutput.Rows.Add(drw1)
    Next drw2
    Return dtbOutput
  EndFunction