?
Solved

insert multiple rows using while loop

Posted on 2006-04-30
3
Medium Priority
?
473 Views
Last Modified: 2012-05-05
I would like to insert multiple rows into oracle table.

script runs and completes no error messages but it doesnt do the insert.

use DBI;
use strict;
      #create connection
my $wdat2ghw = DBI->connect('DBI:Oracle:wdat2ghw', 'student', 'learn')
    || die "Database connection not made: $DBI::errstr";
      #create and execute query
my $strsql = "select employee_id, name  from employee";
my $result = $wdat2ghw->prepare($strsql);
      $result->execute or die "Couldn't execute: " . $wdat2ghw->errstr . "\n";
my @result = $result-> fetchrow_array;

while(@result = $result->fetchrow_array){
    #insert 1 record
     "insert into customer (customer_id, name) values ('@result')";
  };
 
  $result->finish;
  $wdat2ghw->disconnect;
 print "All done";

D
0
Comment
Question by:deNZity
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
Kim Ryan earned 2000 total points
ID: 16574320
use DBI;
use strict;
     #create connection
my $wdat2ghw = DBI->connect('DBI:Oracle:wdat2ghw', 'student', 'learn')
    || die "Database connection not made: $DBI::errstr";
     #create and execute query
my $strsql = "select employee_id, name  from employee";
my $result = $wdat2ghw->prepare($strsql);
     $result->execute or die "Couldn't execute: " . $wdat2ghw->errstr . "\n";
my @result = $result-> fetchrow_array;

# You still need to prepare and execute a statement for the insertion
# But no need to loop on @result, just extract the 2 elements one at a time
$strsql = "insert into customer (customer_id, name) values ('$result[0]','$result[0')";
$result = $wdat2ghw->prepare($strsql);
$result->execute or die "Couldn't execute: " . $wdat2ghw->errstr . "\n";

  $result->finish;
  $wdat2ghw->disconnect;
 print "All done";

0
 

Author Comment

by:deNZity
ID: 16574357
Thanks for the reply, I wondered wether or not I needed to prepare and execute
the sql.
I used ('$result[0]','$result[1]')";

points to you.
0
 
LVL 19

Expert Comment

by:Kim Ryan
ID: 16574401
Glad to help, yes  my error, result[1] is correct. You can also fetch into a hash and the used named parameters like $result{employee_id}.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many time we need to work with multiple files all together. If its windows system then we can use some GUI based editor to accomplish our task. But what if you are on putty or have only CLI(Command Line Interface) as an option to  edit your files. I…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question