Db table to non-alike db table copying

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks,
I have an issue with copying tables. I have a very complicated back end system, and I am copying the information out of that database (4 times aday) into a db that is more usable and I don't have as much of an issue giving others access to. (The primary db will only allow 10 connections as that is all we have licenses for)
Option 1: Using transactions, delete everything in the dest table, copy the new values in, commit the transaction. I don't really like this as it seems that it is over kill on work on the db, but it is the quickest way to code it.
Option 2: Write too loops, one loop goes in to the src db and selects the primary key row and checks to see if the same primary key is in the other database, if it is, stop, if not insert the rec into the dest db. Then do it in reverse but delete from dest db if it is not in the src db.
I know there has to be a better way then what was described. I am hopping that someone can help me. I do know about DBI::COPY to copy one table to anyother, but I have too much translation that has to go on to use it.
Thanks for your help

Comment on Db table to non-alike db table copying

Replies are listed 'Best First'.
Re: Db table to non-alike db table copying by graff (Chancellor) on Feb 06, 2006 at 03:26 UTC
I would expect that the destination DB server has a "bulk load" utility, and this tool should make it possible for a complete replacement of table data to be very simple and very quick, with proper attention to blocking access by others while this is in progress. In other words, go with option 1, and spend your time making sure you use the right tools in the most effective way possible, to make the whole procedure as quick and simple as possible. Option 2 is a lot more work, not only in terms of the initial difficulty of writing the appropriate code, but also in the amount of processing time needed for it to work as intended (along with the associated amount of resource consumption; cpu/server load, 1 of 10 connections tied up for a longer period, etc). Then there's the problem that it will be more difficult to maintain or adapt over time. I think the coding and execution time for a well-designed "option-1" approach will be very attractive: it should be easy and quick to design and run the appropriate query from the source DB, to design and run a suitable perl script to do any additional filtering/editing on the query output (in case some adjustments are easier to do in perl than in sql), and to work out the appropriate bulk load process for the destination table.	[reply]
Re: Db table to non-alike db table copying by g0n (Priest) on Feb 06, 2006 at 08:37 UTC
Alternatively, at risk of pimping my own module, Data::Sync is designed to do this sort of operation. Here's an example: `my $h=Data::Sync->new(); $h->source($sourcedbihandle,"select * from source"); $h->target($targetdbihandle,{index=>"MYPRIMKEY", hashattributes=>[field1,field2]}); $h->mappings(field1=>'targetfield1',field2=>'targetfield2'); $h->transforms(targetfield1=>"s/(\w)+\s(\w+)/$2 $1/", targetfield2=>"lowercase"); $h->run;` [download] That would: read from $sourcedbihandle all records that match the query Remap the source field names to the target field names Transform the data in field1 with the regex, and field2 lowercase Write all records that have changed since the last run. Deletion isn't supported in v0.06 (on CPAN), but the next version has deletion support; I'm currently trying to up the test coverage, but if you want to /msg me I can send you an early copy. Update: Just to clarify - writing only records that have changed since the last run is based on hashing a list of attributes. The 'hashattributes' parameter to target() contains a list of the attributes you want to hash. -------------------------------------------------------------- "If there is such a phenomenon as absolute evil, it consists in treating another human being as a thing." John Brunner, "The Shockwave Rider".	[reply] [d/l]
Re: Db table to non-alike db table copying by srdst13 (Pilgrim) on Feb 06, 2006 at 12:30 UTC
Which databases are you using? Some (postgres, for example), have the ability to create functions to query other databases directly from within them. Knowing the specific database vendors might be useful. That said, I would think a simple dump from one database and restore to the other would be fairly quick. If you absolutely cannot tolerate any downtime (likely to be seconds only), you could try dumping and then restoring to a set of tables different from your production tables and, when the whole process is complete, simply drop the old versions and rename the new versions. Again, the details of these actions and whether they are feasible will depend on your vendors. Sean	[reply]