IMPORT only NEW records SINCE last UPDATE

[eb24]

Hello:

I am not sure how to implement the following, but I believe it entails using DTS, thus my reason for placing this thread here:

On a weekly basis, I need to IMPORT some data located on a remote Oracle DB into SQL Server 2k. Since there is so much data to transfer, I would only like to transfer the data that is new to the table since the last IMPORT, i.e. a week ago and leave behin the OLD data.

Is DTS the correct way to go or do I have more control via DTS with STORED PROCEDURES? Does anyone have any good references for me?

On a similar note, once this Oracle data is IMPORTED into a certain table, I would like to EXPORT some of these NEWLY acquired rows matching certain criteria into another table for auditing purposes. For this scenario, should I implement a TRIGGER UPDATE event here on the first table?

Any advice will be greatly appreciated!

 

[nigelrivett]

Depends on your situation.
Easiest method is to use an SP with a linked server to Oracle if that is available.

You need some method of knowing which recs have been updated on Oracle since the last transfer. It will probably entail inserting into a staging table so that you can update/insert (or delete then insert all) as necessary.

Do not use a datetime field and expect it to be accurate as oracle will hold a different accuracy to sql server - I would use a 5 min bounday if you want to try it.

Make sure you log everything you do including the number of recs transferred.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

One problem you may find is detecting records that have been deleted.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

The best way would be to get the Oracle server to deliver text files of updated records then you can just use bcp to import them (to staging tables again).
It will give you an automatic history of what has happenned and also mean that the export is under control of the same system that owns the data so should be updated in step with any changes.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.