Post reply

join from two different db servers in DTS?

  • April 24, 2002 at 2:46 pm

    #104933

    I am new in using SQL and DTS, I need help from experts. I want to copy data from ServerA to ServerB using DTS. ServerA has EMPLOYESS tables (EMP_ID, EMP_NAME, JOB_ID), ServerB have EMPLOYESS talbe and JOB talbe(JOB_ID, JOB_NAME).

    Now I want to copy data from serverA.EMPLOYEES table to serverb.EMPLOYEES table if serverA.EMPLOYEES.job_id=serverB.JOB.job_id.

    Now I know it sounds cofusing, but bascially I just want to know if I can query two tables from two different db servers in DTS?

    Thanks

  • May 1, 2002 at 9:48 am

    #428207

    I would probablly look into writing one query in the DTS Job using Linked servers running from ServerB. It would look something like this...

    ON SERVERB...

    Create Linked Server to ServerA named servera.

    SELECT * into #temptable FROM servera.<dbname>.dbo.EMPLOYEES a JOIN <dbname>..EMPLOYEES b on a.job_id = b.job_id

    This would then be querying both dataservers. If there is going to be a lot of rows returned, this may not be the best way. But if there aren't.... There are also ways of being more selective in the query using variables and sp_executesql. If you are interested in doing that using a cursor, let me know... I can send you some code samples.

    John Aspenleiter

    jaspenle@strong.com


    John Aspenleiter

Viewing 2 posts - 1 through 1 (of 1 total)

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