Re: Unique sequence number per datafile using SQL Loader

Steve_at_x.com wrote:
[]
> >
> Build a table exactly like the target table. Include a column for the
> sequence number. Load the data into that table. Find the next seq number
> by selecting the highest seq number from the target table and add 1
> (one) to it. Put the seq number value into the load table's seq number.
> Transfer the data from the from the load table to the target table.
>
> Alternatively, Load the data into to target. Have a column for the seq
> number in that table. It is null after the load finishes. Find the
> highest seq value from the target table, add one to it, update the null
> rows to the seq number.

Gagh!
Reasons why this is not a good idea:
1. the load gets slower as the target table gets larger. (because each load must do a new search of the target table for the MAX value).

B. only one file can be loaded at a time (two loaded at the same time both get the same MAX number.)

III. assigning the "file number" is done on the application level after sql*loader is done. (forget to run the number assignment step and you have either rows with no number or rows from two different files with the same number).

Bottom line: use a sequence.

   Ed Received on Fri Nov 17 2006 - 23:00:57 CET