thanks everybody - sometimes you dig yourself into hole when a simpler solution is staring you in the face. If anybody has cracked a method of inserting into column names held in an array, data that is also held in an array - I'd be interested for future reference - for now I'll be linking databases though..
Mark ----- Original Message ----- From: "Andy Crichton" <[EMAIL PROTECTED]> To: "'Mark Martin'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, March 11, 2004 9:37 AM Subject: RE: DBI - copying tables - a better way? > The best way of copying a table in oracle is by creating a database link and > > Usine the CREATE TABLE AS syntax > > CREATE TABLE $table_name AS > SELECT * from [EMAIL PROTECTED] > > > Andy > > > -----Original Message----- > > From: Mark Martin [mailto:[EMAIL PROTECTED] > > Sent: 11 March 2004 09:22 > > To: [EMAIL PROTECTED] > > Subject: DBI - copying tables - a better way? > > > > Hi, > > bin using DBI for a while for data warehousing type stuff and > > up till now I've had to change data on the fly as it moves > > from one database to another : > > > > my $sql01 = qq{SELECT FIELD01 .....FIELDn FROM > > PRODUCTION_TABLE}; my $sql02 = qq{INSERT INTO DATAWAREHOUSE > > VALUES (?,?,?............); my $sth = $dbh->prepare($sql01); > > $sth->execute; > > while (my @row = $sth->fetchrow) { $data1 = $row[0]; > > $data2 = $row[1]; .........manipulate the dat with perl ..... > > and so on........ > > > > But, now, I just want to copy one table into an exact replica > > and to save on coding I want to avoid having to reference > > each DB column and the contents of it per row. Here's what > > I've got so far, the SELECT is working and I know pushing > > onto seperate arrays is wrong (gives HASH(0x820c324) etc. > > with print print "@rows";) but I'm stuck on how to take it further. > > > > #!/usr/bin/perl > > use DBI;use DBD::Oracle; > > $dbh01 = DBI->connect("dbi:Oracle:PROD_DB", "user", "pwrd"); > > $dbh02 = DBI->connect("dbi:Oracle:DATA__WAREHOUSE", "user", > > "pwrd"); my $sth01 = $dbh->prepare(<<SQL); SELECT * FROM PROD_DB SQL > > > > $sth01->execute(); > > while( my $row = $sth01->fetchrow_hashref) { > > foreach my $col (keys %{$row} ) { > > print "$col = $row->{$col}\n"; > > push(@cols,$col); > > push(@rows,$row); > > } > > } > > > > my $fields = join(', ', @cols); > > my $values = join(', ', @rows); > > $sql02 = qq{"INSERT INTO DATA__WAREHOUSE($cols) VALUES ($rows)"}; > > $sth02 = $dbh02->prepare($sql02) or die "Can't prepare SQL > > statement: $DBI::errstr\n"; $sth02->execute(); > > > > > > > > ______________________________________________________________ > > __________ > > This e-mail has been scanned for all viruses by Star > > Internet. The service is powered by MessageLabs. For more > > information on a proactive anti-virus service working around > > the clock, around the globe, visit: > > http://www.star.net.uk > > ______________________________________________________________ > > __________ > > > > > __________________________ > This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. The contents are confidential and may be privileged. > Any views or opinions expressed are those of the sender and may not reflect the views or opinions of the company and the company accepts no liability in respect thereof. > If you have received this email in error please notify [EMAIL PROTECTED] immediately. > > ________________________________________________________________________ > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > ________________________________________________________________________
