DTS to update rows in a target table

  • We have very simple DTS packages.  They move data in and append it to an existing empty table.  Right now I need to read data with dts, find the keys in that incoming table in a table with those keys in another table in sql server, then once a match is found update fields in the table/row found.  If the keys do not exist in the target table there is no row updated (no work done).  

    READ SOURCE -> FIND RECORD IN TARGET -> UPDATE TARGET. 

    What is the easiest way to accomplish this?

  • This is the code I use in an Execute SQL task where I'm joining 2 tables and updating where columns are changed:

    UPDATE dest.dbo.table

    set   col1 = s.col1,

          col2 = s.col2,    

          col3 = s.col3

         

    FROM source.dbo.table AS s INNER JOIN des.dbo.table AS t

    ON t.key_id = s.key_id

    WHERE

    (

       (COALESCE(t.col1, '') <> COALESCE(s.col1, ''))

        OR (COALESCE(t.col2, '') <> COALESCE(s.col2, ''))    

        OR (COALESCE(t.col3, -1) <> COALESCE(s.col3, -1))

    )

    Greg

    Greg

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

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