locked
Copy few column data from one table to another... RRS feed

  • Question

  • Hi,

    I have two tables in my application(Table1 and Table2).
    Table1 - Coulm1, Column2, Column3 Column4, Column5
    Table1 is having some records(say 50 rows).

    I want to copy few records from first table to second table.
    But i want to copy only 3 column data for all rows from first table(Table1) to second table(Table2).

    What is the quickest and easiest way to do this...?

    Thanks,
    IamHuM

    Tuesday, November 17, 2009 5:34 AM

Answers

  • If you're going to need to copy ONLY specific columns of data and not every column that Table2 has in common with Table1, then you're not going to be able to use the ImportRow and you'll have to do something like this:

    DataRow[] rows = Table1.Select("Customer = 'Bob'");
    DataRow NewRow;
    foreach (DataRow row in rows)
    {
        NewRow = Table2.NewRow();
        NewRow["MyTable2Column"] = row["MyTable1Column"];
        Table2.Rows.Add(NewRow);
    }

    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Yichun_Feng Monday, November 23, 2009 2:16 AM
    Tuesday, November 17, 2009 4:24 PM

All replies

  • Assuming that Table2 is already defined with the 3 columns you need, and assuming those 3 columns have the same names as 3 of the columns in Table1, then you can use the ImportRow() method:

    foreach (DataRow row in Table1)
    {
        Table2.ImportRow(row);
    }

    Only the 3 columns that are in common between the two tables will be imported.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Tuesday, November 17, 2009 6:13 AM
  • Thanks Bonnie,

          I'll try this.
          Also Can we use some SELECT 'query' to copy all the rows for specific columns...?

          Can we do this...?

    Thanks,
    IamHuM

    Tuesday, November 17, 2009 6:24 AM
  • Hello,

    Please keep in mind when apply above code.

    Calling NewRow adds a row to the table using the existing table schema, but with default values for the row, and sets the DataRowState to Added . Calling ImportRow preserves the existing DataRowState along with other values in the row.



    Tejas Mer
    Tuesday, November 17, 2009 6:39 AM
  • Hello,

    I have one another way,

    Cosider there are two tables, 1) Customer - Holds customer related information.
                                               2) GermanCustomer [ Which has some redundant information from customer table (which is to be copied from customer table)  & few more fields].

    Now see the following example.

    DataTable customers;// Assume it holds all customers all over the world.
    DataTable GermanCustomers;// only german customers.
    
    DataRow[] copyRows =  customers.Select("CountryRegion = 'Germany'");
    
    foreach (DataRow copyRow in copyRows)
      GermanCustomers.ImportRow(copyRow);
    


    Tejas Mer
    Tuesday, November 17, 2009 6:43 AM
  • Hi Tejas,

    I think your solution will work only if we dont have to copy single column data from parent table to child table.

    If i am wrong can you tell me how i can apply multiple condition...?

    Thanks fot your reply,

    IamHuM

    Tuesday, November 17, 2009 1:33 PM
  • If you're going to need to copy ONLY specific columns of data and not every column that Table2 has in common with Table1, then you're not going to be able to use the ImportRow and you'll have to do something like this:

    DataRow[] rows = Table1.Select("Customer = 'Bob'");
    DataRow NewRow;
    foreach (DataRow row in rows)
    {
        NewRow = Table2.NewRow();
        NewRow["MyTable2Column"] = row["MyTable1Column"];
        Table2.Rows.Add(NewRow);
    }

    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Yichun_Feng Monday, November 23, 2009 2:16 AM
    Tuesday, November 17, 2009 4:24 PM
  • Hello,

    As above code that  Bonnie has mentioned is correct, in that you can give multiple condition as well.

    But take care of one thing for each not null column you must provide value or give them default value, while adding new row.


    Note: Please, Bonnie mam contact me on my email id tejas.be.it@gmail.com, I would like to get some info/Guide from you.


    Tejas Mer
    Tuesday, November 17, 2009 5:50 PM
  • Hi IamHum,
    There is simple and quickest way to copy data from one table to another table

    insert into table2(col1,col2,col3) select col1,col2,col3 from table1

    Hope it helps you.
    PS.Shakeer Hussain Hyderabad
    • Proposed as answer by William Vaughn Wednesday, March 3, 2010 5:50 PM
    Wednesday, March 3, 2010 10:19 AM
  • Huh? This is a case of the "Plumber's Syndrome". (When a plumber is asked a question, he solves it with pipe and valves). The right (best, most efficient, most sensible) answer is to simply write a SQL statement to move the data. Even when done from a program it's far (FAR) easier to code. Mr. Hussain is right. Good job.

    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Wednesday, March 3, 2010 5:52 PM
  • Wait a second ... AFAIK, the OP was talking about DataTables, not database tables. The insert statement is for database tables.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, March 7, 2010 12:39 AM
  • Yes... BonnieB is correct...

    I want copy data from datatables and not DataBase tables...

    Sunday, March 7, 2010 7:00 AM
  • You may do it this way as well:

    DataTable dtNew = new DataTable();

    DataView dv=new DataView(dt,"ID IN (1,2)","ID", DataViewRowState.OriginalRows); // Filter rows that is under consideration

    dtNew = dv.ToTable(true, new string[] { "ID", "sTitle" }); // Select columns you want to see

    Where ID and sTitle are column names.

    You will get expected data in "dtNew" and no additional loop will be required.



    Friday, May 4, 2012 9:45 PM