Wayne, We went throw a sybase->oracle data conversion a couple of years ago. An in-house developed perl script went through the following steps to accomplish the data migration:
- check data types on sybase side - if table didn't exist on oracle, - create compatible structure in oracle - if table did exist in oracle - compare datatypes on both source & destination to insure compatibility - build sql statment to select data from sybase table (datetime, text, image columns required special handling -- precision of less than 1s was lost in migration to oracle; text/image was handled by a second script, after this one was completed.) - build oracle sqlldr control file, with input coming from named pipe - start sqlldr process in background, then start sybase select. - wait for both sides to finish, printing process indication every 1000 rows or so. - count rows on each side. This process was incredibly fast-- the data was moved into oracle as fast as it could be selected from sybase. The use of a named pipe let us not need to ever store the data on disk during the conversion. I believe that we tried using standard insert statements instead of sqlldr, but the performance advantages of sqlldr were worth the additional coding required to make it work. chris On Mon, 8 Apr 2002, Rice, Wayne R (Sybase) wrote: > Thanks! Question: does the Oracle side have to use DBD::ODBC (with Oracle > ODBC) to work due to the date verses datetime datatype issues? I would > prefer to just use DBD::ORACLE in hopes of it being faster. > > -----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. > >
