Bjorn, Yes, I had thought of that and the impact on disk space/fragmentation of constant drops and creates. I've actually run into another problem possibly to do with global names - my insert loks like this :
insert into TESTTABLE select * from [EMAIL PROTECTED] The SID.DOMAIN.COM is what I retrieved with "select GLOBAL_NAME from GLOBAL_NAME;" on the production database But I'm getting the following error on that insert - ORA-00933:SQL command not properly ended Any ideas? Mark ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, March 11, 2004 1:27 PM Subject: RE: DBI - copying tables - a better way? > Mark, > > If you were too recreate a big table every day or even week, it is much > faster to just truncate it and refill it using the same mechanics > > Truncate table b ; > Insert into table b values select * from [EMAIL PROTECTED]; > > Assuming b and a are still having the same columns :) > > Greetz > > > -----Original Message----- > From: Mark Martin [mailto:[EMAIL PROTECTED] > Sent: donderdag 11 maart 2004 14:17 > To: Andy Crichton; [EMAIL PROTECTED] > Subject: Re: DBI - copying tables - a better way? > > 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 > > > ________________________________________________________________________ >
