Insert or Update in one pass?

[Bennie47250]

We currently use a product called Data Junction (DJ) to populate a warehouse. I have been assigned the task of rewriting this process in DTS. Most of what needs to be done seems straightforward except for one aspect. In DJ we are moving records from our AS400 DB2 Order Header table SQL 7.0 version of the same table. As DJ is moving the records, it knows to ether update a record if it already exists in the table, or to add the record if the record does not exist in the table, all in one pass.

I have seen where I can create an Insert or Update process but not do both at once. How do you go about this in DTS?

Also any suggested readings for DTS? I have picked up the “SQL Server 2000 DTS” book by Timothy Peterson and reviewed it. Hoping I could find something a little easier to follow and perhaps something that would walk me thru a few exercises to get started with DTS.

Thanks
Bennie

 

[JohnDTampaBay]

I don't think this is possible in one pass. You will need an UPDATE for the rows that already exist and an INSERT for the rows that don't exist.

http://www.SQLDTS.com

has some great information on many aspects of DTS. Its been a huge help for me. Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.