RE: HOW TO: insert into OracleTableX select * from SybaseTableY

[Gordon . Dewis]

This method will work, though you may want to consider this alternative if
you have a lot of rows.  Create a flat file of from your Sybase table and
use sqlload to load the records into your Oracle table.  Use direct path
loading for better performance.

If you go with the method below, use {AutoCommit => 0} when you set up your
Oracle database handle, otherwise it will do a commit every insert and
you'll encounter a significant performance hit. 

-----Original Message-----
From: Ian Harisay [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 05, 2002 5:42 PM
To: Rice, Wayne R (Sybase); [EMAIL PROTECTED]
Subject: Re: HOW TO: insert into OracleTableX select * from SybaseTableY


If the tables were exactly the same this will work  You may need to tweak
for
your purpose.

$sdbh ## is your sybase database handle.
$odbh ## is your oracle database handle.

$ssth = $sdbh->prepare("select * from table_name");
$ssth->execute();

$osth = $odbh->prepare("insert into table_name (field1, field2) values (?,
?)");

while ( my $rec = $ssth->fetchrow_arrayref() ){
  $osth->execute( @{ $rec } );
}
$ssth->finish;
$osth->finish;

$sdbh->disconnect;
$odbh->disconnect;

"Rice, Wayne R (Sybase)" wrote:
> 
> I wish to do a bulk insert into select from of all rows.
> I have my connection to Oracle (source) and Sybase (destination) set up
and
> working.
> 
> What is the BEST and FASTEST way to do something like this:
> 
> insert into OraX select * from SybY       Note: the col names are the same
> and datatypes as close a possible.
> 
> ????
> 
> Thanks,
> Wayne Rice
> Sybase, Inc.
> Bethesda, MD
> Sybase V-mail: 301-896-1368
> Sybase E-mail: [EMAIL PROTECTED]
> NAVAIR Phone: 301-757-1657
> NAVAIR E-mail: [EMAIL PROTECTED]
> Cell Phone 443 540-7944
>  <<...OLE_Obj...>>