How to merge multiple datatable in to a single datatable

Question

0.00/5 (No votes)

See more:

Hi,

i have 4 datatable. I need to make it one single table and I need to keep all duplicate columns also.Merge methode missing some data.I need to join all tables and to a single one and should keep duplicate columns also.I used below codes but i am missing some coloumn (duplicates also)

What I have tried:

DataTable dtFinal = new DataTable();

            for (int i = 0; i < resultDS.Tables.Count; i++)

            {

                if (i == 0)

                    dtFinal = resultDS.Tables[i].Copy();

                else

                    dtFinal.Merge(resultDs.Tables[i]);

            }



            var ds = new DataSet();

            ds.Tables.Add(dtFinal);

Posted 2-Feb-22 3:52am

Manidas Payyoli

Updated 2-Feb-22 5:49am

Add a Solution

Comments

CHill60 2-Feb-22 10:00am

It might help your explanation if you included some sample data and then the results you expect from that sample data.
If by "keep duplicate columns" you mean that you have a column in each table that has the same name but contains different data then you can't just merge - you cannot have two columns of the same name in the final datatable. It's usual to "adjust" the schema of the 2nd table and then merge it with the first

[no name] 2-Feb-22 10:20am

same column name but diffrent data i have

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

M Imran Ansari · Answer 1 · 2022-02-02T05:49:00

Solution 1

I have created a demo for multiple tables which returning duplicate data as well and after that merge into single table. See the code below:

SqlDataAdapter adapter = new SqlDataAdapter("procGetEmployee", sqlConn);
DataSet resultDS = new DataSet();
adapter.Fill(resultDS, "dsEmployees");

DataTable dtFinal = new DataTable("dtEmployee");
for (int i = 0; i < resultDS.Tables.Count; i++)
{
    dtFinal.Merge(resultDS.Tables[i]);
}

Result: All tables merged into single table that is dtFinal

Posted 2-Feb-22 5:49am

M Imran Ansari

Updated 2-Feb-22 10:00am

v2

Comments

[no name] 2-Feb-22 13:00pm

not working as expected.. here am getting only 3 columns.I dont need to remove duplicates.below am trying

var Dset = new DataSet();

DataTable dt1 = new DataTable();

dt1.Columns.Add("Name", typeof(string));

dt1.Columns.Add("Age", typeof(int));

// Create a DataRow, add Name and Age data, and add to the DataTable

DataRow dr1 = dt1.NewRow();

dr1["Name"] = "Mohammadsd"; // or dr[0]="Mohammad";

dr1["Age"] = 24; // or dr[1]=24;

dt1.Rows.Add(dr1);

Dset.Tables.Add(dt1);

DataTable dt2 = new DataTable();

dt2.Columns.Add("Name", typeof(string));

dt2.Columns.Add("Age", typeof(int));

// Create a DataRow, add Name and Age data, and add to the DataTable

DataRow dr2 = dt2.NewRow();

dr2["Name"] = "Mohammad"; // or dr[0]="Mohammad";

dr2["Age"] = 24; // or dr[1]=24;

dt2.Rows.Add(dr2);

Dset.Tables.Add(dt2);

DataTable dt3 = new DataTable();

dt3.Columns.Add("Name", typeof(string));

dt3.Columns.Add("sex", typeof(int));

// Create a DataRow, add Name and Age data, and add to the DataTable

DataRow dr3 = dt3.NewRow();

dr3["Name"] = "last"; // or dr[0]="Mohammad";

dr3["sex"] = 24; // or dr[1]=24;

dt3.Rows.Add(dr3);

Dset.Tables.Add(dt3);

DataTable dtFinal = new DataTable("dtEmployee");

for (int i = 0; i < Dset.Tables.Count; i++)

{

dtFinal.Merge(Dset.Tables[i]);

}

var ds = new DataSet();

ds.Tables.Add(dtFinal);

M Imran Ansari 2-Feb-22 16:00pm

I have modified the code, please check now.