Using SQL Server DTS Package to Conditionally Insert / Update Rows in Destination Table

Question

I want to create a DTS Package to pull data from an Oracle table into a SQL2K table. How can I insert rows that are not already in the SQL2K table and update rows that already exist in the SQL2K table?

I guess I could truncate and repopulate the entire table or create a temporary table and then do updates/inserts from the temp table into the destination table.

Is there any easier way using DTS?

Thanks,

Rokal

Solution

You can do that in a DTS package using two data driven query tasks: one for the inserts and one for the updates. The data driven query tasks are a bit of a pain to use, but they work. I've also done this (a "merge") in sql server 2000 with an AS/400 database using a dynamic t-sql. You'd write a t-sql script that outputs psql and runs it againt a linked server to the Oracle database.

UPDATE: A DTS "data driven query task" will let you insert|update data from the sql server connection in DTS to an oracle server connection in DTS w/o a temp table or a linked server.

Update2; here's some more info on what I mean: http://www.databasejournal.com/features/mssql/article.php/3315951

http://msdn.microsoft.com/en-us/library/aa933507(SQL.80).aspx

Comments

[Greg]

Booji Boy: Yes, I could create a database link and write two data driven queries. I was hoping there was a way without using linked servers or staging tables.

[Greg]

Timothy Khouri: Your method would require a database link correct?

[Booji Boy]

I founds some additional information on data driven query tasks in DTS. Let me know if it helps - I might be able to dig up (or make) and send you an example.

[Robert]

merge behaves like an insert or update in one statement in tsql