How to merge two datatables, which having different schema?

Question

I have two Datatables shown in below

Table 1

-------------------------
  ID  |   Name 
--------------------------
 1    |  JOHN
 2    |  GEORGE
 3    |  RAGU
--------------------------

Table 2

  ----------
   ID | AGE
  ----------
    1 | 23
    2 | 23
    3 | 22
  ----------

I just want the result as like this..

Result

-------------------------
  ID  |   Name    | AGE
--------------------------
 1    |  JOHN     | 23
 2    |  GEORGE   | 23
 3    |  RAGU     | 22
--------------------------

Thanks..

The data in a table has no inherent sequence. How do you know, that `23` is the "first" age and `22` is the "third"? — Corak, Apr 12 '13 at 09:28
when using datatable merge concept i got the result like 6 rows, first 3 rows contains only ID and Name , the next 3 rows contains only age.. — ragu, Apr 12 '13 at 09:30
If you `SELECT AGE FROM AGETABLE` without any `ORDER BY`, you *might* get the ages in that sequence, but it is not guaranteed you will. Without `ORDER BY` the server is free to give you data in any order it likes. — Corak, Apr 12 '13 at 09:33
@ragu: set relationship between both tables ids in your datatabase — Satinder singh, Apr 12 '13 at 09:39
possible duplicate of [Combining n DataTables into a Single DataTable](http://stackoverflow.com/questions/12278978/combining-n-datatables-into-a-single-datatable) — Guillermo Gutiérrez, Mar 04 '15 at 21:54

score 5 · Accepted Answer · answered Apr 12 '13 at 09:49

you can check this out:

static void Main(string[] args)
{
    Program p = new Program();
    DataTable dt1= p.Get1();
    DataTable dt2 = p.Get2();          
    DataTable dt3 = p.Get3(dt1, dt2);
}
public DataTable Get1()
{
    DataTable dt1 = new DataTable();
    dt1.Columns.Add("ID");
    dt1.Columns.Add("Name");
    dt1.Rows.Add("1", "JOHN");
    dt1.Rows.Add("2", "GEORGE");
    dt1.Rows.Add("3", "RAGU");        
    return dt1;
}

public DataTable Get2()
{
    DataTable dt2 = new DataTable();
    dt2.Columns.Add("AGE");         
    dt2.Rows.Add("23");
    dt2.Rows.Add("23");
    dt2.Rows.Add("22");
    return dt2;
}

public DataTable Get3(DataTable dt1,DataTable dt2)
{
    dt1.Columns.Add("Age");
    for (int i = 0; i < dt1.Rows.Count; i++)
    {
        dt1.Rows[i]["Age"] = dt2.Rows[i]["Age"];
    }
    return dt1;
}

Corak · Answer 2 · 2013-04-12T09:46:46.503

1

Have you heard about INNER JOIN?

Basically, what you want to do is:

SELECT Persons.ID, Persons.Name, Ages.Age 
FROM Persons INNER JOIN Ages ON Persons.ID = Ages.ID

Now you can insert that into another table if you want.

edited Apr 12 '13 at 09:46

answered Apr 12 '13 at 09:40

Corak

2,688
2
21
26

score 1 · Answer 3 · answered Apr 12 '13 at 09:41

1

I assume that this is what you might be looking for

INSERT INTO Result (ID, Name, Age)
SELECT T1.ID, T1.Name, T2.Age
FROM
    Table1 AS T1
INNER JOIN
    Table2 AS T2
ON 
    T1.ID = T2.ID
ORDER BY
    T1.ID

answered Apr 12 '13 at 09:41

Marco Forberg

2,634
5
22
33

How to merge two datatables, which having different schema?

3 Answers3