SELECT from datatable into datatable

Question

I have three DataTables with data:

table1 has columns AGE, FIRST_NAME, LAST_NAME, FAVORITE_COLOR, PHONE
table2 has columns AGE, FIRST_NAME, LAST_NAME, PHONE
table3 has columns AGE, LAST_NAME, FIRST_NAME, FAVORITE_COLOR, PHONE

I also have a fourth table which I need to fill with all data from the three previous tables.

table4 has columns AGE, LAST_NAME, FIRST_NAME, PHONE

Any ideas how this can be done?

Answer 1

It sounds like you are tackling the problem the wrong way; a clue is the way that you are duplicating data and seeking to merge tables together.

This kind of thing would usually be achieved at the data access layer (DAL), i.e. with a better database query.

Perhaps if you supply some more context to the problem you are trying to achieve, we will be able to analyse and see if we can come up with a better solution. Sorry if that sounds patronising, it's not - as you know there are infinite ways of solving a problem in software!

Hope that helps!

Answer 2

Try .ImportRow :

var dtA = new DataTable
{
    Columns =
    {            
        { "Age", typeof(int) },
        { "Middlename", typeof(string) },
        { "Firstname", typeof(string) }
    }
};

dtA.Rows.Add(1, "Yeah", "John");
dtA.Rows.Add(2, "Yo", "Paul");

var dtB = new DataTable
{
    Columns =
    {
        { "Age", typeof(int) },
        { "Firstname", typeof(string) }
    }
};

dtB.Rows.Add(3, "George");
dtB.Rows.Add(4, "Ringo");



foreach (DataRow r in dtA.Rows)
    dtB.ImportRow(r);


foreach (DataRow r in dtB.Rows)
{
    MessageBox.Show(string.Format("{0} {1}", r["Age"], r["Firstname"]));
}

Answer 3

you can try something like this:

DataSet dataset; //dataset with all datatables (table1, table2, table3)
DataTable table4; //datatable with the result "union"
foreach (DataTable dt in dataset.Tables)
{
    foreach (DataRow dr in dt.Rows)
    {
        DataRow nr = table4.NewRow();
        foreach (DataColumn dc in table4.Columns)
        {
            try
            {
                nr[dc.ColumnName] = dr[dc.ColumnName];
            }
            catch
            {
                nr[dc.ColumnName] = "COLUMN NOT FOUND";
            }
        }
        table4.Rows.Add(nr);
    }
}

Answer 4

DataTable has a method called "Merge", so:

table4.Merge(table1, true, MissingSchemaAction.Ignore) // ignores any columns that are not in table4's schema
table4.Merge(table2, true, MissingSchemaAction.Ignore) 
table4.Merge(table3, true, MissingSchemaAction.Ignore) 

Assuming you have given table4 the columns you want first, of course, using

table4.Columns.Add