Another way to do it, using DBI, conceptually (pseudo-code) would be to
* define a unique index on the district 2 table that includes just
the
employee id. After you've done this, then if you try to insert
a row into
the district 2 table with an employee id that already exists in
that table,
Oracle will reject the insert with some kind of a "duplicate
key" error.
* have 2 statement handles - one for reading from district 1, and the
other
for writing to district 2.
* loop - fetch from district 1
eval {
### do INSERT into district 2 "execute" here - if it
fails, it will
### fill $@ with the error message and control will
transfer to 1st
### statement after the eval.
}
if ($@) {
if ($@ =~ /duplicate/) {
### error was caused by trying to insert a
duplicate - just bypass
### this one...
}
else {
### error was NOT caused by trying to insert a
duplicate - we
### better rollback now...
}
}
end loop
Read about Transactions and error handling in the excellent DBI
perldocs by doing
perldoc DBI
at a command prompt.
HTH.
Hardy Merrill
>>> "Reidy, Ron" <[EMAIL PROTECTED]> 02/11/05 9:14 AM >>>
There are so many ways to do this. Below is a PL/SQL way:
#
# not tested
#
my $sth = $dbi->prepare(qq{
BEGIN
FOR d IN (SELECT * FROM district1)
LOOP
BEGIN
INSERT INTO district2 (column_list)
VALUES (d);
EXCEPTION
WHEN dup_val_on_index THEN
NULL;
END;
END LOOP;
END;
}) || die $DBI::errstr;
You could also change the above to use bulk processing (array inserts)
in PL/SQL. I leave this investigation to you and your DBA.
--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-----Original Message-----
From: Robert [mailto:[EMAIL PROTECTED]
Sent: Fri 2/11/2005 6:10 AM
To: dbi-users@perl.org
Cc:
Subject: Re: finding and inserting
Let me see if I can explain it better today. :-)
I have about 200 users in "district 1". I need to add each of those
users
into "district 2" using the same information that they have in
"district 1".
I also need to make sure they are only inserted 1 time. They have a
unique
employeeID, so I know I can use that to make sure that they are
inserted 1
time.
"Ron Reidy" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
What do you mean by 'insert that user into a new district'? Do you
mean
create an oracle account?
--
Ron Reidt
Lead DBA
Array BioPharma, Inc.
-----Original Message-----
From: Robert [mailto:[EMAIL PROTECTED]
Sent: Thu 2/10/2005 11:26 AM
To: dbi-users@perl.org
Cc:
Subject: finding and inserting
I am not sure how to go about this.
I have an Oracle database with users in a district. Every time I have
to add
a new district I have to reset the password. I thought, now why don't I
use
Perl to just copy the users current info and insert that into the new
district with the name of the district changing.
What I need to do is loop through the database and find a user, insert
that
user into the new district, find next user, make sure that user is not
already in the new district to prevent duplicates, insert user into
new
district changing the district information. There will be exceptions
but I
think I can handle those if I can get started on the first part.
My Perl and DBI skills are not quite up to this...so help would be
appreciated.
Robert
This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended
to be for the use of the individual or entity named above. If you are
not
the
intended recipient, please be aware that any disclosure, copying,
distribution
or use of the contents of this information is prohibited. Please notify
the
sender of the delivery error by replying to this message, or notify us
by
telephone (877-633-2436, ext. 0), and then delete it from your system.
This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended
to be for the use of the individual or entity named above. If you are
not the
intended recipient, please be aware that any disclosure, copying,
distribution
or use of the contents of this information is prohibited. Please notify
the
sender of the delivery error by replying to this message, or notify us
by
telephone (877-633-2436, ext. 0), and then delete it from your system.
