Re: Question on referencing loaded columns in SQL Loader

Although technically, there is no reason not to load "dirty" data into your production table, I suggest
you consider this alternative.

Let's say you have production table P into which you desire to load raw data. Sometimes the raw data
contains rows to be ADDed to P, sometimes the raw data contains rows (or fields) which are to
replace rows (or fields) in P.

Create a temporary table T which contains the structure of P; import your data (SQL*Loader?) into
T, verify the correctness of T's data, then do your merge/purge/update process; then truncate T.

If P contains a small number of rows, you are not out much if the raw data contains a pile of
garbage and trashes your production table P. But if P contains, say, 13 million rows, you cannot
afford to search for and correct the corrupted rows, nor restore from a back up (too much downtime).

In our shop, we consider all data not in the database to be foreign data, dirty data, etc. We load it into
a temporary table (P_TEMP), scrub it, verify field contents, etc, before merging it with our production tables.

Me <annardo_at_earthlink.net> wrote in message news:7ukkiq$5s9$1_at_birch.prod.itd.earthlink.net...
> I have a question on referencing previously loaded data in sql loader.
>
> e.g. I have a datafile with these data.
> "A1, "B1", "C1", "D1"
> "A2", "B2", "C2", "D2"
>
> I need to have a control file that would look something like:
> load data
> infile 'test.dat'
> replace
> into table test_table
> fields terminated by "," enclosed by '"'
> (
> column1,
> column2,
> column3,
> column4,
> extracolumn ":column2"
> )
>
> Is it possile for my fifth "extracolumn" reference the value in "column2"
> without SQL*loader thinking that there is an extra data field in the
> datafile? When I run the above, I'll get an error message stating that
> "Column not found before end of record...use TRAILING NULLCOLS". I don't
> want to use TRAILING NULLCOLS, because if I have a lot of fields, then
I'll
> get "Not all variables are bound."
>
> TIA
>
>
>
>
Received on Mon Oct 25 1999 - 14:07:03 CDT