High Performance Loader question
I'm tasked with a job that loads select fields from one table to another. The table is huge so I want to use HP Loader. However, this will be an ongoing job, and we have a need to check the target table for duplicates on subsequent loads. Is there any way that HP Loader can determine that the row it's trying to insert already exists in the target table, and then do an update instead of an insert ? I don't thing so, but thought I'd ask, as my next option seems to be to do a row by row compare, which sucks. Thanks, floyd ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/
You could turn the constraint to filtering and then process the
filtered records with another job to do the updates.
I don't know if this would be worthwhile.
Look at the following in the SQL manual
start violations and stop violations
enable constraint filtering without error
For example
create table mass_inserts(
id integer not null,
mass_insert_value varchar(24),
batch_number integer
) ;
create unique index mass_inserts_0ux on mass_inserts(id) ;
alter table mass_inserts
add constraint(
primary key(id)
constraint mass_inserts_pk
);
-- Set up test
-- Insert the even records.
insert into mass_inserts(id, mass_insert_value, batch_number) select
tabid, tabname, 1 from systables where mod(tabid,2) = 0 ;
-- HPL here
-- HPL may already do this.
start violations table for mass_inserts;
set constraints mass_inserts_pk filtering without error ;
-- insert all records
-- Even records will go to mass_insert_vio table
insert into mass_inserts(id, mass_insert_value, batch_number) select
tabid, tabname, 2 from systables where tabid > 100 ;
stop violations table for mass_inserts;
update mass_inserts set batch_number = 3 where exists(select * frommass_inserts_vio miv where miv.id = mass_inserts.id) ;
drop table mass_inserts_vio;
drop table mass_inserts_dia;
select * from mass_inserts order by id;
I don't know if the update at the end will kill your performance or
not the batch_number could be set to the value of the batch number in
the vio table with the correlated update statement.
Floyd Wellershaus wrote:
> I'm tasked with a job that loads select fields from one table to another. The table is huge so I want to use HP Loader.
> However, this will be an ongoing job, and we have a need to check the target table for duplicates on subsequent loads.
>
> Is there any way that HP Loader can determine that the row it's trying to insert already exists in the target table, and then do an update instead of an insert ?
> I don't thing so, but thought I'd ask, as my next option seems to be to do a row by row compare, which sucks.
>
> Thanks,
> floyd
>
>
>
>
>
>
>
>
>
>
> ========================
> -<<Floyd Wellershaus>>-
> Database Administrator
> Unix Administrator
>
>
>
> email: fwellers@yahoo.com
>
>
> Home: 703-430-0805
>
>
> Cell: 703-477-6045
> ========================
>
>
> http://www.one.org/
> --0-1576530424-1171391159=:49380
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 1695
>
> <html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><DIV></DIV>
> <DIV>I'm tasked with a job that loads select fields from one table to another. The table is huge so I want to use HP Loader.</DIV>
> <DIV>However, this will be an ongoing job, and we have a need to check the target table for duplicates on subsequent loads.</DIV>
> <DIV> </DIV>
> <DIV>Is there any way that HP Loader can determine that the row it's trying to insert already exists in the target table, and then do an update instead of an insert ?</DIV>
> <DIV>I don't thing so, but thought I'd ask, as my next option seems to be to do a row by row compare, which sucks.</DIV>
> <DIV> </DIV>
> <DIV>Thanks,</DIV>
> <DIV>floyd</DIV>
> <DIV><BR> </DIV>
> <DIV><BR>
> <DIV><BR>
> <DIV><BR>
> <DIV><BR>
> <DIV>========================<BR>-<<Floyd Wellershaus>>-<BR>Database Administrator<BR>Unix Administrator</DIV><BR>
> <DIV><BR>email: <A href="mailto:fwellers@yahoo.com">fwellers@yahoo.com</A></DIV><BR>
> <DIV>Home: 703-430-0805</DIV><BR>
> <DIV>Cell: 703-477-6045<BR>========================</DIV><BR>
> <DIV><A href="http://www.one.org/">http://www.one.org/</A></DIV></DIV></DIV></DIV></DIV>;
> <DIV></DIV></div></body></html>
> --0-1576530424-1171391159=:49380--