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

Answer 1

Are you keeping the same primary key values?

If you are you have a number of options, some versions of SQL support the MERGE statement which will update or insert just like you require.

Or you can write your own.

Something along the lines of loading all the rows into a staging table in your SQL database and row by row checking for the existence of your primary key in your main SQL table. If the key exists update the row and if not insert it.

Answer 2

Yes, the primary key values in the source and destination will match.

I was hoping to accomplish this task without the use of a temporary (staging) table.

Also, I am using sql server 2000 so the MERGE statement is not available.

Answer 3

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

Answer 4

There's no way with TSQL to do a INSERT or UPDATE in the same statement, but you could very easily do it in two statements (as you mentioned above).

1) DELETE FROM dbo.WhateverTable WHERE WhateverTableID IN (SELECT WhateverTableID FROM MySource)

2) INSERT INTO dbo.WhateverTable SELECT * FROM MySource

Also, is there any reason why you don't want to use a temp table?

Answer 5

Since I need to create two connections in the DTS Package, one for Oracle and one for SQL Server, I was hoping I could programmatically interact with both connections without the creation of a linked server or staging table.

for example, something like this would be great (unfortunately it doesn't work):

Insert into DTSConnection1.DestinationTable(columns) select * from DTSConnection2.SourceTable as source where not exists ( select null from DTSConnection1.DestinationTable as destination where source.PrimaryKeyColumn = destination.PrimaryKeyColumn)

Answer 6

DELETE FROM dbo.WhateverTable WHERE WhateverTableID IN (SELECT WhateverTableID FROM MySource)

might be pretty slow, use join instead.

Delete a from firstTable a join secondTable b on a.id = b.id