plsql- Table enrichment from another's table data

Assume table 'Source' that is filled with data every hour with an in-place procedure. I want to run a procedure that will fill my new table 'NEW' with ONLY the new rows of the target table SOURCE each time the pros is executed,keeping in mind that the new table MUST always keep all the time the already inserted data (i mean that a solution each time the process to insert into...NEW then insert into to a temporary table distinct values, 开发者_如何学JAVAdelete NEW, insert into from temp etc is not helpful).

---

If i got right what you want, and would be, insert only new records from SOURCE to NEW, there are tons of ways of doing this. Here are some of them:

1) Create a trigger on SOURCE that automatically inserts into NEW so you don't have to worry about a thing.

2) Use statement like this to select only new rows from SOURCE. This one assumes that primary keys that are the same on both tables.

INSERT INTO NEW
SELECT * FROM SOURCE s1 WHERE NOT EXISTS (SELECT 1 FROM NEW n1 WHERE n1.key=s1.key)

3) Use materialized view & mv log feature. This one is a bit complex and i suggest looking into oracle documentation or some other resources if you are not familiar with it.

4) Change your procedure that inserts into SOURCE to also insert into NEW.

Of course, you have to figure out what to do if there are possible updates on SOURCE. I can explain these methods in detail if you want.

---

Voted up dsmoljanovic's solution. One reason why a procedural solution (with timestamps or sequences) isn't good for this is uncommitted data.

Take an example:

At 02:55:00: 75 rows are added to SOURCE and are timestamped '02:55:00'
At 02:55:30: The 75 row insert is committed
At 02:59:55: 100 rows are added to SOURCE and are timestamped '02:59:55'
At 03:00:00: Your process kicks off and selects from source
At 03:00:20: The 100 row insert is committed

The process won't see those 100 rows (since they are not committed) and the next time it runs, it may miss them if it looks for rows timestamped after 03:00:00.