how to copy only the columns in a DataTable to another DataTable?

Question

score 131 · Accepted Answer · edited Feb 17 '17 at 17:46

131

DataTable.Clone() should do the trick.

DataTable newTable = originalTable.Clone();

edited Feb 17 '17 at 17:46

Community

1
1

answered Jun 01 '10 at 12:56

codingbadger

42,678
13
95
110

First DataTable 238 columns and Second Datatable has 107 columns using DataTable newTable = originalTable.Clone(); then All columns copy to direct Second Table – Ghotekar Rahul Dec 30 '20 at 10:13

score 36 · Answer 2 · answered Jun 01 '10 at 16:15

If only the columns are required then DataTable.Clone() can be used. With Clone function only the schema will be copied. But DataTable.Copy() copies both the structure and data

E.g.

DataTable dt = new DataTable();
dt.Columns.Add("Column Name");
dt.Rows.Add("Column Data");
DataTable dt1 = dt.Clone();
DataTable dt2 = dt.Copy();

dt1 will have only the one column but dt2 will have one column with one row.

score 3 · Answer 3 · edited Jun 24 '14 at 07:53

3

Datatable.Clone is slow for large tables. I'm currently using this:

Dim target As DataTable = 
    New DataView(source, "1=2", Nothing, DataViewRowState.CurrentRows)
    .ToTable()

Note that this only copies the structure of source table, not the data.

edited Jun 24 '14 at 07:53

Ruskin

5,721
4
45
62

answered Jun 24 '14 at 07:29

qery

39
1

3

OP asked for C# solution. This is VB – AaA Dec 28 '15 at 02:40

Bhasin · Answer 4 · 2018-07-27T06:00:01.893

If you want to copy the DataTable to another DataTable of different Schema Structure then you can do this:

Firstly Clone the first DataType so that you can only get the structure.
Then alter the newly created structure as per your need and then copy the data to newly created DataTable .

So:

Dim dt1 As New DataTable
dt1 = dtExcelData.Clone()
dt1.Columns(17).DataType = System.Type.GetType("System.Decimal")
dt1.Columns(26).DataType = System.Type.GetType("System.Decimal")
dt1.Columns(30).DataType = System.Type.GetType("System.Decimal")
dt1.Columns(35).DataType = System.Type.GetType("System.Decimal")
dt1.Columns(38).DataType = System.Type.GetType("System.Decimal")
dt1 = dtprevious.Copy()

Hence you get the same DataTable but revised structure

score 1 · Answer 5 · answered Aug 17 '17 at 04:55

If you want the structure of a particular data table(dataTable1) with column headers (without data) into another data table(dataTable2), you can follow the below code:

        DataTable dataTable2 = dataTable1.Clone();
        dataTable2.Clear();

Now you can fill dataTable2 according to your condition. :)

score 0 · Answer 6 · answered Dec 04 '12 at 09:51

The DataTable.Clone() method works great when you want to create a completely new DataTable, but there might be cases where you would want to add the schema columns from one DataTable to another existing DataTable.

For example, if you've derived a new subclass from DataTable, and want to import schema information into it, you couldn't use Clone().

E.g.:

public class CoolNewTable : DataTable {
   public void FillFromReader(DbDataReader reader) {
       // We want to get the schema information (i.e. columns) from the 
       // DbDataReader and 
       // import it into *this* DataTable, NOT a new one.
       DataTable schema = reader.GetSchemaTable(); 
       //GetSchemaTable() returns a DataTable with the columns we want.

       ImportSchema(this, schema); // <--- how do we do this?
   }
}

The answer is just to create new DataColumns in the existing DataTable using the schema table's columns as templates.

I.e. the code for ImportSchema would be something like this:

void ImportSchema(DataTable dest, DataTable source) {
    foreach(var c in source.Columns)
        dest.Columns.Add(c);
}

or, if you're using Linq:

void ImportSchema(DataTable dest, DataTable source) {
    var cols = source.Columns.Cast<DataColumn>().ToArray();
    dest.Columns.AddRange(cols);
}

This was just one example of a situation where you might want to copy schema/columns from one DataTable into another one without using Clone() to create a completely new DataTable. I'm sure I've come across several others as well.

if I try to do a loop like your suggested iteration shown above in 'ImportSchema' - I get an error saying the column already belongs to another DataTable. In other words, you can't loop an existing DataTable's column collection and assign those specific instances over to a different DataTable instance. — bkwdesign, Sep 15 '14 at 17:51
I think this might be the way to write out ImportSchema Dim ms As New IO.MemoryStream srcDataTable.WriteXmlSchema(ms) ms.Seek(0, IO.SeekOrigin.Begin) destDataTable.ReadXmlSchema(ms) — bkwdesign, Sep 15 '14 at 18:21
The last two examples will fail with the error message: System.ArgumentException: 'Column 'Name' already belongs to another DataTable.' — BillW, Jan 03 '21 at 04:15

how to copy only the columns in a DataTable to another DataTable?

6 Answers6