Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2006
    Posts
    7

    Question Unanswered: Server To Server

    Please provide me T-SQL script which will transfer table data from one SQL Sever to another SQL Server ?

    Note : Database and table on both server are exactly same in all aspects.

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    I assume that the servers can communicate with each other. What you want is either:

    sp_addlinkedserver and then insert into ... select. (Both documented fairly well in Books Online)

    or

    Import and Export Wizard in SSIS (or similar feature in DTS if you are using SQL Server 2000).
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Oct 2006
    Posts
    7
    I want T-SQL Script. You have just guven an overview. If you can explain in brief will be nice.

    Thanks.

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    In brief yes:

    sp_addlinkedserver 'TargetServer','SQL Server'

    to set up connection to the other server. Then we transfer the data:

    insert into TargetServer.owner.table select * from owner.table;

    If you are having identity values or other columns that cannot be inserted to you'll have to omit those columns, of course.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    if you have lots of data, you'll be better off using bcp rather than linked servers.

  6. #6
    Join Date
    Oct 2006
    Posts
    7
    I had never tried BCP will you give bit of idea.

    Thanks a lot.

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    BOL explains what bcp is better than I. have a read:

    http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •