Re: SQLLDR Question

This is what the client wants.

They have a specific set of records in a table. There is another record set that can be FTPd. This may contain new records or records with the same primary key that are slightly different in the rest of the record. The client wants to be able to choose which one to keep. Their's or the new one.

I thought about doing a primary key, then run a cursor to check for dupes and delete the dupes. Also it would put a flag on the ones that were left to say whether its a new record or a changed record. "RSH" <RSH_Oracle_at_worldnet.att.net> wrote in message news:Twsh8.18090$gK2.1352773_at_bgtnsc04-news.ops.worldnet.att.net...
> Uh, not simple!
>
> I'd have to practically write the thing in pseudocode to answer all your
> questions.
>
> But yes, triggers will fire and homemade PL/SQL functions will run etc if
> you run SQLLDR in conventional path and don't do tricky things like
> PARALLEL. Your load will not be lighting fast, but you have all this stuff
> you want to do, either direct path load a scratch table and have a package
> of procedures that you call afterward, or conventional path and a whole
> bunch of triggers.
>
> In any case you should be enforcing duplicate record problems by using a
> primary key on the tables where you do not want dupes, that does not need
> triggers or anything else-- unless you want to do something fancy like
> conditionally replace a row based on something or other, then you'd need
> something that would delete the old row first before the replacement row
> comes in, or it will bounce. But keep the PK! Trust me.
>
> If this secondary table may have dupe records and the users can pick one
to
> replace in the other table or something like that, you ought to have a
> composite non-unique index that corresponds with the columns in the PK of
> the other table, that will ease selects.
>
> Lastly, this seems really complicated; ask yourself if simpler ways would
do
> or does your business process absolutely require this kind of mechanism?
>
> RSH.
> "Ryan Gaffuri" <rkg100_at_erols.com> wrote in message
> news:6757365f.0203060743.48867b58_at_posting.google.com...
> > I need to run a daily batch to load data into a table. This table will
> > be an exact duplicate in structure to another table that already
> > exists.
> >
> > The user will then have the option to move the New or Changed records
> > over to the original table.
> >
> > Im wondering the best way to do this. First off since the batch runs
> > every night and the user can perform the process when they feel like
> > it we may get duplicate records in the load table.
> >
> > So one way to do this would be to load the data into a temp table and
> > as part of the patch only insert the records that are not duplicates
> > to the load table.
> > As part of this I also want to check the original table to make sure
> > that these are not duplicate records. I also want to flag a column as
> > new or changed based on whether one column in the load table matches
> > the original table, but one or more of the other tables does not. New
> > will be if the one column is different and changed will be if the one
> > column is the same.
> >
> > So for example.
> >
> > What is the fast way to run this? Im gonna run it from a dos batch and
> > have it log in to do sqlddr and run the pl/sql script. So no need for
> > triggers?
> >
> > Also, will an insert trigger fire off of sqlldr? If so could I use
> > statement level trigger to enforce rules when data is loaded into the
> > table from sqlldr?
>
>
Received on Wed Mar 06 2002 - 14:37:17 CST