DTS Join on Source & Destination

• burtwalker

  Newbie

  Points: 5

  More actions

  December 10, 2001 at 7:14 pm

  #104601

  I'm new to DTS, using SQL 7.0. Is there a way to set up my source select statements using a join on both the source and the destination tables? Example: I'm trying to update a table on the Destination server with all new records from an identical table on the Source server. I simply want to insert the newest ones based on whether the Source table has key values greater than the Destination table. I need to at least have a WHERE clause comparing fields from both tables. I realize there are other ways to deal with this (replication, etc.), but I have other requirements for joining the source and destination tables as a criteria on my source selects.

• David Benoit

  SSC-Dedicated

  Points: 34562

  More actions

  December 11, 2001 at 7:15 am

  #420988

  I'm assuming you are using a Execute SQL task or a transform data task and if so, I believe you still have to have a linked server in order to do this. It has been a while since I tried this so I can try later today and let you know.

  If anyone else knows for sure, please post.

  David

  David

  @SQLTentmaker

  “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738043

  More actions

  December 11, 2001 at 10:30 am

  #421003

  You'd need to use a linked server and join them as a single source.

  You can use the multi data pump thing, but I've never liked it. I'd prefer to use a linked server or move my source and compare using native sql on the destination.

  Steve Jones

  steve@dkranch.net