truncate if exists
I have a question regarding truncating/updating/deleting records in a table.
I have a table containing data and i want to update it with new data, but want to retain the differences in the "old" table-data compared to the data I want to update it with.
Old table ==> Add data ==> New table
Now, how do I do that?
There are a few ways this can be done. Are you in an OLTP environment with single records being added per transaction or are you batch loading?
One simple idea would be to create a trigger on the table - the trigger could then load old and news values into a audit table (a separate table that looks very much like the original, but contains extra columns to show both old and new values). Of course, the audit table doesn't have to have a pair of columns for old and new if you have some type of unique identifier - then you just need to keep the old values and some sort of update date/time so you can trace back. In addition, I'd keep around the database user or application user id if you want to track who did it ...
Again, plenty of ways here - that's just one example. Would be useful to know what database version you are on and the type of processing it does (OLTP, data warehouse, something else?). The way you "add data" can have a major impact on things like trigger performance.
Last edited by pbarmak; 12-08-2006 at 12:29 PM.
Thanks alot for your reply.
I'm looking at two versions of a table and want to merge them ommiting entries that are equal for both tables.
I want to batch-load data into the table using a sql-script.
The script contains updated information and I want to replace similar entries in the table retaining all other information.
I'm using 10g2 enterprice edition and sqldeveloper.
There is a merge statement in Oracle since 9i.
when mached then update
when not mached then delete
I am not sure if that's exactly what you need but worths to look at
Exactly what I was looking for.
So How do I write this script then?
Click Here to Expand Forum to Full Width