locked
Transfer data from a table to another table RRS feed

  • Question

  • Hello

    I want to transfer data from a table in one server to another table in a different server, I want to do this on a nightly job, what will be the best way, please advise, thank you.

    Wednesday, July 2, 2014 6:48 PM

Answers

  • Hi , You can create SSIS package with data flow Control flow and call that package in sal server Agent job on a specific schedule. This package you can enhance depend your Needs. Hope this helps. Regards Please mark this as answer if it is what you Looking for.
    • Proposed as answer by Shanky_621MVP Wednesday, July 2, 2014 7:18 PM
    • Marked as answer by DataNerdE Thursday, July 3, 2014 4:21 PM
    Wednesday, July 2, 2014 6:53 PM
  • Multiple options

    1. Create linked server  and use INSERT..SELECT * FROM [Server].[DB].dbo.Table

    2. Use OPENROWSET

    http://searchsqlserver.techtarget.com/tip/Using-the-OPENROWSET-function-in-SQL-Server

    3. Use export import wizard

    http://www.leniel.net/2011/07/import-export-data-sql-server-database.html#sthash.l4fgNNdV.dpbs


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Shanky_621MVP Wednesday, July 2, 2014 7:18 PM
    • Marked as answer by DataNerdE Thursday, July 3, 2014 4:21 PM
    Wednesday, July 2, 2014 6:58 PM
  • If data volume is huge dont go for linked server it might cause issue in such case import export wizard or SSIS package should be preferred

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Marked as answer by DataNerdE Thursday, July 3, 2014 4:21 PM
    Wednesday, July 2, 2014 7:18 PM
  • I would go with SSIS package and add incremental logic to load data every night. Check this YouTube video for guidance.

    All the best!!


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    • Marked as answer by DataNerdE Thursday, July 3, 2014 4:04 PM
    Thursday, July 3, 2014 3:30 PM

All replies

  • I'd setup a linked server and do a simple insert MyTable select * from ServerA.MyTable.
    • Proposed as answer by Shanky_621MVP Wednesday, July 2, 2014 7:18 PM
    Wednesday, July 2, 2014 6:50 PM
  • Hi , You can create SSIS package with data flow Control flow and call that package in sal server Agent job on a specific schedule. This package you can enhance depend your Needs. Hope this helps. Regards Please mark this as answer if it is what you Looking for.
    • Proposed as answer by Shanky_621MVP Wednesday, July 2, 2014 7:18 PM
    • Marked as answer by DataNerdE Thursday, July 3, 2014 4:21 PM
    Wednesday, July 2, 2014 6:53 PM
  • Multiple options

    1. Create linked server  and use INSERT..SELECT * FROM [Server].[DB].dbo.Table

    2. Use OPENROWSET

    http://searchsqlserver.techtarget.com/tip/Using-the-OPENROWSET-function-in-SQL-Server

    3. Use export import wizard

    http://www.leniel.net/2011/07/import-export-data-sql-server-database.html#sthash.l4fgNNdV.dpbs


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Shanky_621MVP Wednesday, July 2, 2014 7:18 PM
    • Marked as answer by DataNerdE Thursday, July 3, 2014 4:21 PM
    Wednesday, July 2, 2014 6:58 PM
  • If data volume is huge dont go for linked server it might cause issue in such case import export wizard or SSIS package should be preferred

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Marked as answer by DataNerdE Thursday, July 3, 2014 4:21 PM
    Wednesday, July 2, 2014 7:18 PM
  • If there's a lot of data to be transferred then you can still use a linked server you just have to transfer the data in small batches rather than using one large transaction.
    Thursday, July 3, 2014 2:09 PM
  • I would go with SSIS package and add incremental logic to load data every night. Check this YouTube video for guidance.

    All the best!!


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    • Marked as answer by DataNerdE Thursday, July 3, 2014 4:04 PM
    Thursday, July 3, 2014 3:30 PM