Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    Memphis, TN
    Posts
    17

    Question Unanswered: Replacing contents of table

    I have an EMPLOYEES table that I populate from a flat file. The flat file updates daily (about 120,000 records). My question is, what's the best way to handle updating the table from the flat file? What I've been doing is deleting all records from the table, then importing the flat file into the empty table. But even if this is the best way to get the data updated, it leaves open the possibility that a user will try to access the table while it is empty or in the process of importing. What's the professional way of preventing this? (I'm using Oracle 8i, soon to upgrade to 9i).

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    I think sqlloader with the replace option will do this. I forget if it replaces only the matching on the PK or if it replaces only if it matches everything.

    the truncate option works well too. So that sqlloader removes all rows and then loads your data.

    All this is not ideal for you however.

    I would load the data into a temp-table, then update through a proc which could commit either line-by-line or by a group of records.

    Also, since you only want to update the rows that have changes, you currently are updating/deleting 120k rows, when in reality, you might only have to UPDATE 10 of those rows.

    With your proc, you could create a cursor to select each matching row by PK. Then you create an additional FOR UPDATE cursor for any rows that need updated data, change the data, commit, then goto the next row that would need updating.

    Create some indexes for the temp-table beforehand and this process should work pretty fast.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Sep 2003
    Location
    Memphis, TN
    Posts
    17
    Thanks, Duck. Next question: when using temporary tables, is it smarter to create a table for that use and leave it in the database, or to create and drop the table before and after use?
    -Keith

  4. #4
    Join Date
    Sep 2003
    Posts
    27
    There's no need to drop the table each time.
    If you truncate it each time then it takes up no space. You can put some privileges on it to keep other users out.

    Alternatively, you could use a temporary table which will only hold data for one session (it'll be invisible to other users) and truncate itself when you disconnect.

    This can be done using something like:
    "CREATE GLOBAL TEMPORARY TABLE TEMP_EMPLOYEES(.....) ON COMMIT PRESERVE"

Posting Permissions

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