How to merge Multiple datatable in a single datatable

Question

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)

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);

Sachin Singh · Answer

You need to Call the Copy() method in each iteration if you want to preserve the table DataTable dtFinal = new DataTable(); for ( int i = 0; i { dtFinal = resultDS.Tables[i].Copy(); dtFinal.Merge(resultDs.Tables[i]); } var ds = new DataSet(); ds.Tables.Add(dtFinal);

Muhammad Imran Ansari · Answer

I have three tables like: In the dataset we have also 3 tables, two tables have same columns and third table have different: Code to Merge into single Table: SqlDataAdapter adapter = new SqlDataAdapter("procGetEmployee", sqlConn); DataSet resultDS = new DataSet(); adapter.Fill(resultDS, "dsEmployees"); DataTable dtFinal = new DataTable("dtEmployee"); for (int i = 0; i { dtFinal.Merge(resultDS.Tables[i]); } Resulted dtFinal:

Sujeet Raman · Answer

how can i do this in excel interop? I need to expoert this 3 table together to one excel sheet side by side. i have 3 datatable ,each dt have 2 cloms so my excel should have 2+2+2= 6 coloms.How can i add like this? below is my methods.where i have to change? please help public static void CreateExcelSheet(DataSet result, string excelPath) { var result= 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); result.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); result.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); result.Tables.Add(dt3); object misValue = System.Reflection.Missing.Value; Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue); for (int i = 0; i { DataTable table = ds.Tables[i]; Excel._Worksheet xlWorkSheet = xlWorkBook.Sheets.Count ? (Excel._Worksheet)xlWorkBook.Sheets.Add(After: xlWorkBook.Sheets[xlWorkBook.Sheets.Count]) : (Excel._Worksheet)xlWorkBook.Sheets[ 1]; //same sheet xlWorkSheet.Columns.NumberFormat = "@"; AddDataToWorksheet(xlWorkSheet, table); xlWorkSheet.Columns.AutoFit(); xlWorkSheet.Rows.AutoFit(); Marshal.ReleaseComObject(xlWorkSheet); } xlWorkBook.SaveAs(excelPath); xlWorkBook.Close(); xlApp.Quit(); Marshal.ReleaseComObject(xlWorkBook); Marshal.ReleaseComObject(xlApp); } private static void AddDataToWorksheet(Excel._Worksheet sheet, DataTable table) { sheet.Name = "Report"; for (int i = 0; i { sheet.Cells[1, i + 1] = table.Columns[i].ColumnName; } for (int i = 0; i { int rowNumber = i + 2; DataRow row = table.Rows[i]; for (int j = 0; j { sheet.Cells[rowNumber, j + 1] = Convert.ToString(row[j]); } } }

Muhammad Imran Ansari · Answer

Yes but if you have different columns name of each datatable then you can easly merge into single table. for reference see the following link: https://www.aspsnippets.com/questions/116527/Merge-two-DataTable-columns-and-rows-into-one-in-ASPNet/

Sujeet Raman · Answer

any way i need to make this table and export to excel excel colom shoud have 6 coloms.

Muhammad Imran Ansari · Answer

@Sabeesh Your statement "I need to keep all duplicate columns" but in datatable you can not duplicate Column names by using datatable and if you try to add duplicate column into datatable, you will get the exception "A column named 'ColumnName' already belongs to this Data Table. You need to add/append with column name while merging.

Sujeet Raman · Answer

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 { dtFinal.Merge(Dset.Tables[i]); } var ds = new DataSet(); ds.Tables.Add(dtFinal);//here i need to get 6 colms

Sujeet Raman · Answer

still i my duplicate colom names adding.. if i have 3 tables each table have 3 coloms. in final table i shud get 3+3+3=9 coloms.Thas not happening here.(colom name same but value may be different)

Muhammad Imran Ansari · Answer

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: Returning Tables: Code: SqlDataAdapter adapter = new SqlDataAdapter("procGetEmployee", sqlConn); DataSet resultDS = new DataSet(); adapter.Fill(resultDS, "dsEmployees"); DataTable dtFinal = new DataTable("dtEmployee"); for (int i = 0; i { dtFinal.Merge(resultDS.Tables[i]); } var ds = new DataSet(); ds.Tables.Add(dtFinal); Result:

Adrian Nufc Tether · Answer

DataTable table1 = new DataTable("Items"); // Add columns DataColumn idColumn = new DataColumn("id", typeof(System.Int32)); DataColumn itemColumn = new DataColumn("item", typeof(System.Int32)); table1.Columns.Add(idColumn); table1.Columns.Add(itemColumn); // Set the primary key column. table1.PrimaryKey = new DataColumn[] { idColumn }; // Add RowChanged event handler for the table. table1.RowChanged += new System.Data.DataRowChangeEventHandler(Row_Changed); // Add ten rows. DataRow row; for (int i = 0; i { row = table1.NewRow(); row["id"] = i; row["item"] = i; table1.Rows.Add(row); } // Accept changes. table1.AcceptChanges(); PrintValues(table1, "Original values"); // Create a second DataTable identical to the first. DataTable table2 = table1.Clone(); // Add column to the second column, so that the // schemas no longer match. table2.Columns.Add("newColumn", typeof(System.String)); // Add three rows. Note that the id column can't be the // same as existing rows in the original table. row = table2.NewRow(); row["id"] = 14; row["item"] = 774; row["newColumn"] = "new column 1"; table2.Rows.Add(row); row = table2.NewRow(); row["id"] = 12; row["item"] = 555; row["newColumn"] = "new column 2"; table2.Rows.Add(row); row = table2.NewRow(); row["id"] = 13; row["item"] = 665; row["newColumn"] = "new column 3"; table2.Rows.Add(row); // Merge table2 into the table1. Console.WriteLine("Merging"); table1.Merge(table2, false, MissingSchemaAction.Add); PrintValues(table1, "Merged With table1, schema added"); } private static void Row_Changed(object sender, DataRowChangeEventArgs e) { Console.WriteLine("Row changed {0}	{1}", e.Action, e.Row.ItemArray[0]); } private static void PrintValues(DataTable table, string label) { // Display the values in the supplied DataTable: Console.WriteLine(label); foreach (DataRow row in table.Rows) { foreach (DataColumn col in table.Columns) { Console.Write("	 " + row[col].ToString()); } Console.WriteLine(); } }