none
Simple and easy copying of data between tables RRS feed

  • Question

  • I found a lot af samples doing it in lot of different ways - whitch method shall I choose ????????

    What is the most SIMPLE, EASYest and SHORTest peace of code to:

    1) read a row from one table

    2) make some editing to the data

    3) add the data to another table

    Best regards

    KSor, Denmark

    Friday, January 30, 2009 8:19 AM

Answers

  • If you have two databases and want to move data from one to the other, I would not use Dataset for this. I would instead use the appropriate Ado.Net Provider instead.

    For instance if I am moving data from an Oledb source to Sql server, I would open a connection using OledbConnection create the required OleDbCommand, Execute it. Then Open a connection to the Sql server machine and do inserts or updates to the data in that table instead.

    I would not load all the data into a Dataset and then move it from one database to the other. The other way described above will do the same thing faster and also use much less memory. If you are loading in into a Dataset/DataTable you must be aware that there are memory limitations with it. You may not be able to read all the rows from one into a DataTable, hence the streaming method would be better. Another option when moving data between databases if Sql Server is one of them is to use SSIS.

    Can you provide more details on the specifics of what you are trying to do? Now if you need to edit the data in the transfer from one to the other it might make more sense to use the dataset because you can simply databind it to a grid make edits and push the data to the other source. You must be aware of the memory related limitations however.

    Thanks

    Chris Robinson

    PM - DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, January 30, 2009 11:59 PM

All replies

  • Are you talking about tables at the database, or in the dataset?

    If you just want to copy data in the database, you can do it straight through SQL, for example: 

    INSERT INTO Table2 (a, b, c)  
    SELECT a + 1, b + 1, c + 1 FROM Table1 

    Table2 would contain the contents of Table1, each value incremented by 1. Obviously you can use more complex expressions.

    Friday, January 30, 2009 11:51 PM
  • If you have two databases and want to move data from one to the other, I would not use Dataset for this. I would instead use the appropriate Ado.Net Provider instead.

    For instance if I am moving data from an Oledb source to Sql server, I would open a connection using OledbConnection create the required OleDbCommand, Execute it. Then Open a connection to the Sql server machine and do inserts or updates to the data in that table instead.

    I would not load all the data into a Dataset and then move it from one database to the other. The other way described above will do the same thing faster and also use much less memory. If you are loading in into a Dataset/DataTable you must be aware that there are memory limitations with it. You may not be able to read all the rows from one into a DataTable, hence the streaming method would be better. Another option when moving data between databases if Sql Server is one of them is to use SSIS.

    Can you provide more details on the specifics of what you are trying to do? Now if you need to edit the data in the transfer from one to the other it might make more sense to use the dataset because you can simply databind it to a grid make edits and push the data to the other source. You must be aware of the memory related limitations however.

    Thanks

    Chris Robinson

    PM - DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, January 30, 2009 11:59 PM
  • Thanks Chris Robinson for a good explanation of the problem !

    I like "Ockham's razor" and allmost every sample code I found is "polluted" with a lot of assumptions not needed to clear up the problem in focus. 

    What I'm trying to do is just UNDERSTAND what I'm doing when I'm doing something !

    The problem is that I sometimes do NOTHING because I can't get the idea of many of the sample codes found on the internet - there are often NO explanation at all !

    I have made an sample with datasets/datatables and I wil now try make an sample with "connections" instead - a link to a SHORT code would be nice - a code sample where Ockham's razor HAS been used would be even better.


    Best regards KSor, Denmark
    Sunday, February 1, 2009 6:41 AM