truncate if exists
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: truncate if exists

  1. #1
    Join Date
    Dec 2006
    Posts
    5

    truncate if exists

    Hi there.

    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.

    E.g.

    Old table ==> Add data ==> New table


    Now, how do I do that?


    Nicolai

  2. #2
    Join Date
    Nov 2006
    Posts
    13
    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 01:29 PM.
    Paul Barmaksezian
    Founder, Surfliner Services, LLC
    http://www.surflinerservices.com

  3. #3
    Join Date
    Dec 2006
    Posts
    5
    Hi.

    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.

    Nicolai

  4. #4
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    There is a merge statement in Oracle since 9i.
    Merge source
    into Destination
    using join_condition
    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

    Regards

  5. #5
    Join Date
    Dec 2006
    Posts
    5
    Exactly what I was looking for.

    So How do I write this script then?

    Nicolai

  6. #6
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630

  7. #7
    Join Date
    Dec 2006
    Posts
    5


    Regards,

    Nicolai

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width