Post reply

Comparing and updating data in two tables from different DBs

  • 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.

  • November 16, 2005 at 8:01 pm

    #604337

    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

  • November 17, 2005 at 9:23 am

    #604453

    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

  • November 18, 2005 at 7:06 am

    #604625

    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 

  • November 18, 2005 at 7:27 pm

    #604770

    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

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

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