locked
Best way to transfer data RRS feed

  • Question

  • I would like to transfer 15 Lac rows from one table to another which option is quickest and accurate mechanism to implement.

    Preferable automated way is better

     

    Wednesday, January 4, 2012 5:32 PM

Answers

  • You have various methods for transferring data from one table to another. Some methods are:

    • The INSERT INTO Method
    • The DTS Import/Export Wizard Method
    • The BCP/Bulk Insert Method

    For details about which is the best method for you click here:

    http://www.databasejournal.com/features/mssql/article.php/3507171/Transferring-Data-from-One-Table-to-Another.htm

    There are a number of ways to move data from one table to another. Some options are better then others, depending on what data is being moved, and the volume. The INSERT INTO method is not very fast for large amounts of records, where as the BCP/BULK INSERT method can quickly load large volumes of records. If you prefer a GUI driven method, then the DTS Import/Export method is for you. There are other methods out there that you could use, but these three should provide you with alternatives depending on your data movement criteria.


    Read my blog (Blog)
    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you
    • Proposed as answer by PrinceLucifer Thursday, January 5, 2012 6:58 AM
    • Marked as answer by Maggie Luo Tuesday, January 31, 2012 9:01 PM
    Thursday, January 5, 2012 6:25 AM

All replies

  • Sidx, did you try to use SSIS ?

     

    Regards.


    Erickson Ricci
    Microsoft MCITP 2008 Admin, MCTS SQL Server 2005, 2008
    Visite o meu blog: http://ericksonricci.wordpress.com
    Me siga no twitter: @EricksonRicci
    LinkedIN: http://br.linkedin.com/in/ericksonricci
    e-mail: ericksonfabricio@gmail.com
    **Ajude a melhorar o sistema de busca do fórum.Marque a(s) resposta(s) que foram úteis**
    **Se esta resposta solucionou a questão, então, por favor, marque-a como resposta.**
    Wednesday, January 4, 2012 5:53 PM
  • BCP should be the right tool for you. Have a look at this article  http://www.bidn.com/blogs/DustinRyan/ssis/1813/using-bcp-utility-in-ssis

    Thank you,


    Anup | Database Consultant

    Blog: www.sqlsailor.com Twitter: Follow me !

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, January 4, 2012 5:54 PM
  • Hi,

    I think you can select into and put your where condition to get your rows.

     

     

    I hope this is helpful.

     

    Elmozamil Elamir

    MyBlog


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid
    http://elmozamil.blogspot.com
    Thursday, January 5, 2012 5:20 AM
  • You have various methods for transferring data from one table to another. Some methods are:

    • The INSERT INTO Method
    • The DTS Import/Export Wizard Method
    • The BCP/Bulk Insert Method

    For details about which is the best method for you click here:

    http://www.databasejournal.com/features/mssql/article.php/3507171/Transferring-Data-from-One-Table-to-Another.htm

    There are a number of ways to move data from one table to another. Some options are better then others, depending on what data is being moved, and the volume. The INSERT INTO method is not very fast for large amounts of records, where as the BCP/BULK INSERT method can quickly load large volumes of records. If you prefer a GUI driven method, then the DTS Import/Export method is for you. There are other methods out there that you could use, but these three should provide you with alternatives depending on your data movement criteria.


    Read my blog (Blog)
    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you
    • Proposed as answer by PrinceLucifer Thursday, January 5, 2012 6:58 AM
    • Marked as answer by Maggie Luo Tuesday, January 31, 2012 9:01 PM
    Thursday, January 5, 2012 6:25 AM
  • Hi sidx,

    the best way to transfer Lacs of rows would be using a SSIS package.

    added advantages of SSIS are:

    First, it manages memory very efficiently, which can result in big performance improvements compared to T-SQL alone.

    Second, the graphical interface lets you build large, complex and reliable transforms much more easily than hand-crafted T-SQL.

    And third, SSIS lets you more easily interact with additional external sources, which can be very handy for things like data cleansing.

    Logging, error handling is best in ssis.

        From my experience the room for improvement with SSIS packages is great. We saw 10 fold improvements in our data warehouse environment when we took some of the heavy hitting stored procedures and put them in SSIS packages. The memory utilization of SSIS (in this situation anyways) made all of the difference.

    • Proposed as answer by Hunt_SQL Friday, January 6, 2012 5:22 AM
    Friday, January 6, 2012 5:22 AM