Nested Query to Retrieve Only New Records??

[jjohns09]

I have created a DTS package that performs the following steps:

1) DROP TABLE ACTIVITY
2) CREATE TABLE ACTIVITY
3) Insert Records from the following query in ACTIVITY

SELECT
SCTR.ACTIVITY.NUMBER_SC,
SCTR.ACTIVITY.TYPE,
SCTR.ACTIVITY.DATESTAMP,
SCTR.ACTIVITY.OPERATOR,
SCTR.PROBSUMMARY.LOCATION
FROM SCTR.PROBSUMMARY, SCTR.ACTIVITY
WHERE (SCTR.PROBSUMMARY.NUMBER_SC = SCTR.ACTIVITY.NUMBER_SC)

This works fine and dandy. However, there are now more than 1.3 million records and the query can take well over an hour. What I have thought of doing is only querying for records where the DATESTAMP is greater than the maximum DATESTAMP in my target table. Something like this:

WHERE (SCTR.PROBSUMMARY.NUMBER_SC = SCTR.ACTIVITY.NUMBER_SC)
AND SCTR.ACTIVITY.DATESTAMP >= TO_DATE('6/13/2002 12:13:27', 'MM/DD/YYYY hh24:mi:ss')

This works great and completes in under 5 minutes inserting about 5,000 records. However, this requires me to manually change the date in the query. I have attempted the following, but it doesn't seem to work:

WHERE (SCTR.PROBSUMMARY.NUMBER_SC = SCTR.ACTIVITY.NUMBER_SC)
AND SCTR.ACTIVITY.DATESTAMP >= (SELECT MAX(DATESTAMP) FROM
SCTR.ACTIVITY))

I have never had the patience to see if this actually works because I have stopped the DTS after an hour.

Can anyone help???

Thank in advance for taking the time to read this long winded explanation of my issue.

Jeff Johnson

 

[wilk]

Have you thought of adding just a flag to the database to let you know if the row has been transfered before or not. This can be done as a trigger so the transfered flag is no on edit of a row or insert of a new row. This will get around the problem of having to worry about the date.

Don't know if this helps but it is the way I would do it.

 

[jjohns09]

Wilk,

Thank you for the reply. The only problem with this that I can see is that my source is an Oracle database that I do not have write access to. My target database is a SQL db.

Jeff