Merge 2 Datatables and Store in a New One

Merge 2 DataTables and store in a new one

The Merge method takes the values from the second table and merges them in with the first table, so the first will now hold the values from both.

If you want to preserve both of the original tables, you could copy the original first, then merge:

dtAll = dtOne.Copy();
dtAll.Merge(dtTwo);

Combining two c# datatables into one

If your objective is just to merge data without considering the relationship between the two data then you can add two more columns into first datatable and through loop get data from second table and assign them to first datatable columns. The way the data is received will be the way data will be saved in first datatable.

public DataTable MergeData(DataTable dtFirst,DataTable dtSecond)
{
    dtFirst.Columns.Add("LocalAuthority");
    dtFirst.Columns.Add("AverageSpeed");
    for (int i = 0; i < dtFirst.Rows.Count; i++)
    {
        dtFirst.Rows[i]["LocalAuthority"] = dtSecond.Rows[i]["LocalAuthority"];
        dtFirst.Rows[i]["AverageSpeed"] = dtSecond.Rows[i]["AverageSpeed"];
    }
    return dtFirst;
}

Now , you need to pass datatable as parameter in following method.

MergeData(allTables.ElementAt(0), allTables.ElementAt(1));

Merging 2 datatables in to 1 datatable with same number of rows.

Without knowing more about the design of these tables, some of this is speculation.

What it sounds like you want to perform is a JOIN. For example, if you have one table that looks like:

StateId, StateName

and another table that looks like

EmployeeId, EmployeeName, StateId

and you want to end up with a result set that looks like

EmployeeId, EmployeeName, StateId, StateName

You would perform the following query:

SELECT Employee.EmployeeId, Employee.EmployeeName, Employee.StateId, State.StateName
FROM Employee
INNER JOIN State ON Employee.StateId = State.StateId

This gives you a resultset but doesn't update any data. Again, speculating on your dataset, I'm assuming that your version of the Employee table might look like the resultset:

EmployeeId, EmployeeName, StateId, StateName

but with StateName in need of being populated. In this case, you could write the query:

UPDATE Employee
SET Employee.StateName = State.StateName
FROM Employee
INNER JOIN State ON Employee.StateId = State.StateId

Tested in SQL Server.

Merge two datatables?

You could temporarily change the name of the second column and still use Merge:

const string OriginalName = "name";
const string TemporaryName = "temp";
table2.Columns[OriginalName].ColumnName = TemporaryName;
table1.Merge(table2);
table1.Columns[TemporaryName].ColumnName = OriginalName;
table2.Columns[TemporaryName].ColumnName = OriginalName;

---

Related Topics