Post reply

Copy data between 3 servers

  • March 29, 2006 at 6:59 am

    #167786

    Hy,

    I've this problem:

    I need to select data from a table placed in a DB on SRV1 with join on a

    table placed in another DB on SRV2 and insert this data in a table placed in a DB on SRV3....

    how I can do that??

     

     

  • March 30, 2006 at 1:29 am

    #629729

    --Ciao domenico... I hope the following script will help...

    --On SRV3 you can execute the following commands

    ---- Inform the system for the other servers

    exec sp_addlinkedserver 'SRV1', N'SQL Server'

    go

    exec sp_addlinkedserver 'SRV2', N'SQL Server'

    go

    --Execute the actual Insert

    USE db_Italy

    insert into MyTable (col1, col2, col3)

    select (col1, col2 col3)

    from [SRV1].DB1.dbo.TableUNO, [SRV2].DB2.dbo.TableDUE

    WHERE [SRV1].DB1.dbo.TableUNO.ID= [SRV2].DB2.dbo.TableDUE.ID

    go

    -- Drop the link (not the servers!!)

    exec sp_dropserver 'SRV1', 'droplogins'

    go

    exec sp_dropserver 'SRV2', 'droplogins'

    go

    ------------
    When you 've got a hammer, everything starts to look like a nail...

  • March 30, 2006 at 5:03 am

    #629777

    Thanks for help,

    now I try ....

     

    Domenico

  • March 30, 2006 at 9:22 am

    #629903

    The linked server approach assumes that the two servers can see each other. The example given shows no security info, so it is also assuming they are in the same domain and trusted logins will work.

    There may be network or security reasons why this won't work.  For instance, servers in my office can link to the database servers at our web hosting site over our VPN, but the remote servers can't link back to our office.  The links also need SQL login username/password because the servers are in different domains.  We can join tables from both sites in a connection to a local server, but not on the remote servers.

    It wouldn't be difficult to imagine a scenario where the DTS package is running from a location that can connect to both server1 and server2, but no linked server definition is possible to do a join.  In this case you might use a lookup transform in DTS (poor performance for large amounts of data), or transfer the data from one server to a temp table on the other to allow a join.

    If you upgrade to SQL 2005, SSIS has a join transform that could do the job.

  • March 31, 2006 at 12:44 am

    #630019

    Hy Scott,

    All Work fine. however..I hope to upgrade to SQLServer 2005 in few month.....

    Thanks

    Domenico 

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply