Copy rows from one Datatable to another DataTable?

Question

How can I copy specific rows from DataTable to another Datable in c#? There will be more than one row.

score 292 · Accepted Answer · answered Oct 26 '10 at 02:36

292

foreach (DataRow dr in dataTable1.Rows) {
    if (/* some condition */)
        dataTable2.Rows.Add(dr.ItemArray);
}

The above example assumes that dataTable1 and dataTable2 have the same number, type and order of columns.

answered Oct 26 '10 at 02:36

Bradley Smith

13,353
4
44
57

27

Wouldn't this result in "This row already belongs to another table." – McArthey Jun 06 '12 at 19:59
15

@McArthey No, it wouldn't; a new row is being created from the values in the existing row. The row itself is not being added to the other DataTable. – Bradley Smith Jun 07 '12 at 00:31
This is resulting in "Input array is longer than the number of columns in this table". – Vinod Oct 21 '12 at 11:46
1

@Vinod If that's the case, then your two tables have a different number of columns. I stated clearly in my answer that this method only works when the two tables have the same number, type and order of columns. – Bradley Smith Oct 22 '12 at 00:37
@BradleySmith it gives me this exception . "This row already belongs to another table." – Dawood Ahmed Jul 11 '13 at 06:48
21

@DawoodAbbasi That would only happen if you left out the `ItemArray` part at the end of the expression. Make sure you are adding the row's values, not the row itself. – Bradley Smith Jul 11 '13 at 06:50
1

@BradleySmith now it gives me this exception "Input array is longer than the number of columns in this table." – Dawood Ahmed Jul 11 '13 at 07:02
2

@DawoodAbbasi As I said in my original answer, "dataTable1 and dataTable2 have the same number, type and order of columns". If you're getting that exception then the tables must not have the same number of columns. – Bradley Smith Jul 11 '13 at 07:11
@BradleySmith how to give same columns to the new DataTable?? How to Create the second DataTable?? – Dawood Ahmed Jul 11 '13 at 07:20
5

@DawoodAbbasi Refer to the MSDN documentation for the `DataTable.Clone` method: http://msdn.microsoft.com/en-us/library/system.data.datatable.clone.aspx – Bradley Smith Jul 11 '13 at 07:26
11

Though your answer technically is correct, your code sample doesn't address your assumptions. The answer by @RageeshGr handles all the assumptions and IMHO is written more concisely and is less error prone. – chris.nesbit1 Oct 24 '13 at 12:13
First you need to populate the other table with columns. Otherwise it will give error. "Input array is longer than the number of columns in this table" – Muhammad Abbas Mar 09 '18 at 09:52
1

@Ravi, so what is the alternative if this solution causes decrease in performance? – Sam Feb 27 '19 at 12:41
This proved a useful solution to me to copy all of the contents of one datatable into another datatable which already contained data. – Robert Quinn Sep 21 '19 at 20:51
The Rows.Add(dr.ItemArray) was the fastest by a bit more than ImportRow in a 5000 iteration test with a strongly typed DataTable. Total size of the source datatable was 675 rows. I am building picklist data sources with this. LOAD CACHE: 0:0.10.367 LINQ QRY CACHE: 0:0.0.0 Iterations: 5000 COPYTO w/ MERGE AVG (ms): 12.07034856 MERGE NO TEMP TABLE AVG (ms): 16.70951208 FOREACH ADD Rows.Add(r.ItemArray) AVG (ms): 11.6610181 FOREACH ImportRow(r) AVG (ms): 11.81321906 – Allen Aug 20 '20 at 11:18
Here is one of the tests.... beginTime = DateTime.Now; picklistDt.Clear(); foreach (ParamtrDtRow r in qry.AsEnumerable()) picklistDt.Rows.Add(r.ItemArray); endTime = DateTime.Now; diff = endTime.Subtract(beginTime); diffForEachAddRowSum += diff; res = String.Format("{0}: {1}:{2}.{3}.{4}", "FOREACH Rows.Add(r.ItemArray)", diff.Hours, diff.Minutes, diff.Seconds, diff.Milliseconds); – Allen Aug 20 '20 at 11:18

score 122 · Answer 2 · edited Oct 29 '12 at 06:59

122

Copy Specified Rows from Table to another

// here dttablenew is a new Table  and dttableOld is table Which having the data 

dttableNew  = dttableOld.Clone();  

foreach (DataRow drtableOld in dttableOld.Rows)
{
   if (/*put some Condition */)
   {
      dtTableNew.ImportRow(drtableOld);
   }
}

edited Oct 29 '12 at 06:59

Himanshu

31,810
31
111
133

answered Oct 12 '12 at 05:56

Rageesh Geetha Raman

1,409
1
10
13

If I am no longer need to use dttableOld after import, do I still need to use Clone()? – Sam Feb 27 '19 at 12:40
Great and practical! Thanks! – Mayer Spitz Jun 25 '20 at 18:28
2

@Sam regarding your question, Clone is to copy the table structure, if your table is already the same data-type, then you don't need it. – Mayer Spitz Jun 25 '20 at 18:29
There is better way to do this. entityColumnMetadata = changedColumnMetadata.AsEnumerable().Where(dataRow => entityName.Equals(dataRow["EntityName"])).CopyToDataTable(); – yuvraj Nov 06 '20 at 08:03
+1 Good answer. Worth pointing out that `DataTable::ImportRow` is more tolerant, compared with `DataTable::Rows::Add(...)`, when it comes to the number and order of columns . If a column is missing in the imported row, the value is set to NULL. – AlainD Apr 06 '22 at 22:32

score 23 · Answer 3 · answered Dec 16 '11 at 07:22

23

Try This

    String matchString="ID0001"//assuming we have to find rows having key=ID0001
    DataTable dtTarget = new DataTable();
    dtTarget = dtSource.Clone();
    DataRow[] rowsToCopy;
    rowsToCopy = dtSource.Select("key='" + matchString + "'");
    foreach (DataRow temp in rowsToCopy)
    {
        dtTarget.ImportRow(temp);
    }

answered Dec 16 '11 at 07:22

Zia

921
5
14

@ManojSavalia, so what is the alternative that doesn't cost performance? – Sam Feb 27 '19 at 12:42

score 21 · Answer 4 · answered Nov 08 '12 at 17:06

21

Check this out, you may like it (previously, please, clone table1 to table2):

table1.AsEnumerable().Take(recodCount).CopyToDataTable(table2,LoadOption.OverwriteChanges);

Or:

table1.AsEnumerable().Where ( yourcondition  ) .CopyToDataTable(table2,LoadOption.OverwriteChanges);

answered Nov 08 '12 at 17:06

Estevez

1,014
8
13

This is a much better approach because it uses built-in capability to range-copy or even select rows from one DataTable to another without explicitly iterating through each, unlike all of the foreach loop, .ForEach method based answers. – David Burg Aug 30 '18 at 22:36

score 16 · Answer 5 · edited Oct 20 '17 at 17:46

16

Supported in: 4, 3.5 SP1, you can now just call a method on the object.

DataTable dataTable2 = dataTable1.Copy()

edited Oct 20 '17 at 17:46

Philip Brack

1,340
14
26

answered Feb 28 '14 at 07:00

Amir

782
1
9
19

3

Technically this creates a copy of a datatable. While it's not explicitly stated in the question, it's possible that dataTable2 already exists and has other rows in it which we don't want to lose. Also, the question specifically states "specific rows", whereas this only handles all rows. – Matt Mar 08 '16 at 22:24

score 7 · Answer 6 · edited Jul 21 '16 at 08:43

7

As a result of the other posts, this is the shortest I could get:

DataTable destTable = sourceTable.Clone();
sourceTable.AsEnumerable().Where(row => /* condition */ ).ToList().ForEach(row => destTable.ImportRow(row));

edited Jul 21 '16 at 08:43

Community

1
1

answered Aug 13 '15 at 16:32

Meiel

81
1
3

Which is basically a foreach. Also, you're not filtering through a condition, as requested by the OP. – Eric Wu Jul 20 '16 at 19:51
Now if I clear out the `sourceTable` will the `destTable` also clear? – Si8 Feb 20 '17 at 15:00
I want to be able to assign a value and clear out the original variable without clearing out the destination. – Si8 Feb 20 '17 at 15:05

score 5 · Answer 7 · answered Jul 20 '20 at 02:22

5

I've created an easy way to do this issue

 DataTable newTable = oldtable.Clone();    
 for (int i = 0; i < oldtable.Rows.Count; i++)
 {
   DataRow drNew = newTable.NewRow();    
   drNew.ItemArray = oldtable.Rows[i].ItemArray;    
   newTable.Rows.Add(drNew);   
 }

answered Jul 20 '20 at 02:22

Willie Cheng

7,679
13
55
68

An even easier way to do this is instead of using a `for`, use a `foreach (Datarow dr...)` – Freerey Jan 15 '21 at 20:03

score 3 · Answer 8 · edited Dec 11 '21 at 00:50

3

I needed to copy rows from multiple tables with the same structure into a new table to be used as a datasource for datagridview:

// Generate DataTable[] alltables from multiple datatables

DataTable newTable = alltables[0].Clone();
foreach (DataTable dt in alltables)
{
   for (int i = 0; i < dt.Rows.Count; i++)
      newTable.Rows.Add(dt.Rows[i].ItemArray);
}

edited Dec 11 '21 at 00:50

Peter Csala

17,736
16
35
75

answered Dec 10 '21 at 19:29

Peter-Irkshnoi

31
1

score 2 · Answer 9 · answered Jan 03 '14 at 06:06

below sample would be the fastest way to copy one row. each cell is being copied based on the column name. in case you dont need a specific cell to copy then have a try catch or add if. if your going to copy more than 1 row then loop the code below.

DataRow dr = dataset1.Tables[0].NewRow();
for (int i = 0; i < dataset1.Tables[1].Columns.Count; i++)
{
    dr[dataset1.Tables[1].Columns[i].ColumnName] = dataset1.Tables[1].Rows[0][i];
}

datasetReport.Tables[0].Rows.Add(dr);

dataset1.Tables[1].Rows[0][i]; change the index 0 to your specified row index or you can use a variable if your going to loop or if its going to be logical

score 1 · Answer 10 · edited Jan 04 '17 at 09:50

1

 private void CopyDataTable(DataTable table){
     // Create an object variable for the copy.
     DataTable copyDataTable;
     copyDataTable = table.Copy();
     // Insert code to work with the copy.
 }

edited Jan 04 '17 at 09:50

Roopendra

7,674
16
65
92

answered Jan 04 '17 at 08:48

your code works perfect to me, is a very simple code ,thanks – Esraa_92 Jun 28 '17 at 13:17

score 0 · Answer 11 · answered Jan 02 '19 at 20:08

0

To copy whole datatable just do this:

DataGridView sourceGrid = this.dataGridView1;
DataGridView targetGrid = this.dataGridView2;
targetGrid.DataSource = sourceGrid.DataSource;

answered Jan 02 '19 at 20:08

inspiron

121
2

2

the question is on how to copy specific rows from a data table, not the whole thing. – jtate Jan 02 '19 at 20:13

score 0 · Answer 12 · answered Mar 28 '19 at 11:22

For those who want single command SQL query for that:

INSERT INTO TABLE002 
(COL001_MEM_ID, COL002_MEM_NAME, COL002_MEM_ADD, COL002_CREATE_USER_C, COL002_CREATE_S)
SELECT COL001_MEM_ID, COL001_MEM_NAME, COL001_MEM_ADD, COL001_CREATE_USER_C, COL001_CREATE_S
FROM TABLE001;

This query will copy data from TABLE001 to TABLE002 and we assume that both columns had different column names.

Column names are mapped one-to-one like:

COL001_MEM_ID -> COL001_MEM_ID

COL001_MEM_NAME -> COL002_MEM_NAME

COL001_MEM_ADD -> COL002_MEM_ADD

COL001_CREATE_USER_C -> COL002_CREATE_USER_C

COL002_CREATE_S -> COL002_CREATE_S

You can also specify where clause, if you need some condition.

score 0 · Answer 13 · answered Nov 06 '20 at 08:04

There is better way to do this.

DataTable targetDataTable = new DataTable(); targetDataTable = changedColumnMetadata.AsEnumerable().Where(dataRow => entityName.Equals(dataRow["EntityName"])).CopyToDataTable();

Please try this and let me know in case of any issues.

score 0 · Answer 14 · edited Jan 11 '21 at 13:17

0

You can do it calling the DataTable.Copy() method, for example:

   DataSet ds = new DataSet();
   System.Data.DataTable dt = new System.Data.DataTable();
   dt = _BOSearchView.DS.Tables[BusLib.TPV.TableName.SearchView].Copy();
   ds.Tables.Add(dt);
   UltGrdSaleExcel.SetDataBinding(ds, dt.TableName, true);

edited Jan 11 '21 at 13:17

Cleptus

3,446
4
28
34

answered Jan 11 '21 at 11:10

score 0 · Answer 15 · edited Sep 21 '22 at 08:09

0

use Merge, this will append all rows of newDataTable with oldDateTale

oldDateTale.Merge(newDataTable);

edited Sep 21 '22 at 08:09

mikus

3,042
1
30
40

answered Sep 19 '22 at 13:46

mohamed omar

1

Copy rows from one Datatable to another DataTable?

15 Answers15

Linked

Related