none
Copy table to a database on a different SQL Server

    Question

  • I would like to copy a table from one database to another.

    Is there any easy way to do this if the databases are on two different SQL Server's, without having to loop through every record in the original table and inserting it into the new table?

    Monday, July 15, 2013 9:20 AM

Answers

All replies

  • If secondary table already contains some data then I’d suggest using some third party comparison tool. Third party tools are not necessary, using them is just more convenient. You can do everything natively.

    Here is a good thread on similar topic with a lot more examples on how to do this:

    http://stackoverflow.com/questions/187770/copy-tables-from-one-database-to-another-in-sql-server

    Monday, July 15, 2013 9:25 AM
  • 1) Create a linked server  and issue

    SELECT * INTO tmp FROM servername.dbname.dbo.tablename

    2) SSIS (oledb control with fast load option)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, July 15, 2013 9:25 AM
    Answerer
  • You can copy the table through Linked server. Read

    Linked Servers (Database Engine)


    Many Thanks & Best Regards, Hua Min

    Monday, July 15, 2013 9:26 AM
  • Hello,

    You can use a linked server  to the other server, that you can access the database on the other server like each other; you only have to use a four-part qualifier like

    INSERT INTO LokalDatabase.dbo.TableName
    SELECT *
    FROM LinkedServer.RemoteDatabase.dbo.TableName


    Olaf Helper

    Blog Xing

    Monday, July 15, 2013 9:27 AM
  • You have plenty of options

    SSIS

    Data transfer wizard(Taks>Export Data)

    Linked server


    Satheesh

    Monday, July 15, 2013 9:28 AM
  • Hi

     For you scenario lot of options are there,

    1.SSIS-Intergration service

    2.OpenRowset - will take more time, move one by one.

    3.Data Import/Export wizards

    4.Linked Server.

    5.backup and Restore the database.

    5 option is best.

    Monday, July 15, 2013 12:26 PM