CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqldts
user24358
user24358

Reputation: 135

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

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

Upvotes: 2

Views: 10439

Answers (5)

Timothy Khouri
Timothy Khouri

Reputation: 31885

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).

Statement 1:

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

Statement 2:

INSERT INTO dbo.WhateverTable 
SELECT * FROM MySource

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

Upvotes: 0

P2theK
P2theK

Reputation: 1

Try:

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

It might be pretty slow, use join instead:

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

Upvotes: 0

Booji Boy
Booji Boy

Reputation: 4582

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

Upvotes: 1

user24358
user24358

Reputation: 135

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.

Upvotes: 0

Robert
Robert

Reputation: 1845

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.

Upvotes: 0

Related Questions