Comparing and updating data in two tables from different DBs

Question

Comparing and updating data in two tables from different DBs

cbarus

SSCommitted

Points: 1909
More actions
November 16, 2005 at 6:19 pm

#67015

Guys- I am new to DTS and I need to write a DTS package to do the following.
1. Server1.Database1.Table1 has 2 columns: AgentId, Status
Data: 1, Active
          2, Inactive
2. Server2.Database2.Table2 has 2 columns: AgentId, Status
Data:   1
           2
I need to compare the 2 tables with the key being AgentId and Server1 being my source. I need to update the Status in the Server2 with whatever value is in Server1. May be I am unable to explain right, but I think the solution should be simple. I should not use, bcp, linked servers or staging tables.
Any pointers are greatly appreciated.

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

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

philcart SSC-Forever Points: 47795 More actions · Answer 1

If you can use Linked Servers then this is probably the way to go.

Update t2
Set t2.status = t1.status
From dbo.table2 t2 
Inner Join server1.db1.dbo.table1 t1 
On t1.agentid = t2.agentid

If a Linked server is not viable, due to network connectivity, record volume, etc... your next best option would be to create a staging table on server2. Then you would use DTS to transfer the data from server1 to the staging table on server2. This allows you to run basically the same SQL statement as above.

If the record volume is very small, you might be able to get away with using a Data Driven Query in DTS.

--------------------
Colt 45 - the original point and click interface

cbarus SSCommitted Points: 1909 More actions · Answer 2

PhillCart- As mentioned in my post, I cannot use Linked Server or Staging tables... Long story, why I can't use them.

You were talking about data driven query. Could you me some direction on this?

Thanks

Prema sambandam SSCommitted Points: 1825 More actions · Answer 3

In DTS designer, create source database and target database.

create execute sql task in dts designer. In that, write update statement as philcart mentioned. Create "on success" workflow from source to execute sql task. In sql task existing connection tab, select target database. save the package and execute it. It will work

philcart SSC-Forever Points: 47795 More actions · Answer 4

Sorry, mis-read the "...should not..." line.

The Data Driven Query is a task in DTS. Basically you give a source SQL Statement and a set of insert/update/delete statements to run on the destination. You then put together an ActiveXScript task to determine which of the insert/update/delete statements to execute.

As the task has to execute this ActiveXScript for each row in the source it can be very detrimental to performance. That is why I suggest creating a staging table on the destination and running simple T-SQL statements from there.

--------------------
Colt 45 - the original point and click interface