Geeks With Blogs
Chris B's Blog My Blog about Hobby Electronics

I'm not a DTS wizard by any means. I don't use it a whole lot, and always consider writing a program to move data around instead (Although I rarely do since DTS is usually quicker and can be set up pretty quickly) since I don't find it very intuitive. When I needed to move a list of users from one database to another, either inserting if they didn't exist, or updating information if they did, I wanted to see if DTS had a simple solution.

Now I had to do a lot of searching online before I figured out how to do what I wanted, but I found a decent solution that was fairly simple - the Data Driven Query task. Here are the steps that I used for my specific solution:

  • Specify your source table
    • this is where you'll get the information to feed to the destination table
  • Specify your binding table
    • This is usually your destination table, but represents the data that you'll be transforming from your source and using to drive your Insert/Update queries
  • Create a lookup
    • Since I needed to look in my destination database to see if an insert or update was necessary, I created a lookup for the destionation based on a unique field
  • Transformation
    • Here is where the majority of the magic happens. As you would in a normal transformation, specify your source and destination fields. Note that this doesn't actually insert data as it normally would, but provides you with the information to feed to your update/insert queries.
    • Execute your lookup to see if the record already exists in the destination table. If it does, use the DTSTransformstat_UpdateQuery status instead of DTSTransformstat_InsertQuery.
  • Specify your queries
    • Specify your Insert/Update queries. For any values that will be coming from the source database, use a ? in place of the value. If you typed it manually, click on the Parse/Show Parameters button.
    • Once your parameters are showing, specify the field the value will be coming from.

And there you have it!

BTW, I'm sure this would still work in SQL Server 2005, but I'm stuck in 2000 for the meantime. I hope the task is a bit less confusing in 2005 also.

Technorati : , ,

Powered by Zoundry

Posted on Wednesday, July 12, 2006 1:46 PM | Back to top

Comments on this post: SQL Server Tip - updating data from disparate databases

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Christopher Berg | Powered by: