Still having a hard time transferring records table to table

Re: Still having a hard time transferring records table to table

> Back about May 10, I presented a difficulty I was having trying to
> add in more (distinct) records from one
> MySQL table into another that permanently holds the most important
> data that I have. Despite help from
> a couple of you in the forum and numerous efforts by me since,
> success on my efforts have been denied
> .
>
> First, some review and introduction:
> This is for a knowledge base on all woods around the world. The more
> woods can be reported,
> the more successful is the project. I have named the project 'TAXA"
> (..short for taxonomy). Many of the
> other features I have wanted to add have been coming on quite well
> but this task is still evading any success.
> If you care to directly view what this is about, take a look at
> http://www.prowebcanada.com/taxa/.
> In fact, you can escape the frames menu and go right to where species
> only are displayed:
> http://www.prowebcanada.com/taxa/alphaspecies.php.
> There are 4 major entry points to all the data but you have to be
> concerned with the 'species' level.
>
> The data there is stored in a table named 'species' (ie. -
> species.sql). All tables in the project are in database 'taxa'. The
> more (botanical) names of woods that I can find and add in, the more
> successful is the whole project. So far I am reporting just over
> 6,200 different woods (as I say, all in species.sql).
>
> The Thorny Problem
> Presently I use all kinds of sources to find new woods. The largest
> list of woods I have is in a sql file
> called 'tervuren', with 11,337 wood names. That is WAAYY larger than
> what is in species.sql so there
> is a huge interest in copying over these records to species.sql It
> would be a huge boost that even with
> duplicates avoided, that would almost double the number of woods I
> could report!
>
> That is easy said but I have almost being tearing my hair out (...
> and I am almost bald anyway ;-) )
> trying to accomplish this. To prepare for this:
>
> - I protected the integrity of my original files
> (species.sql and tervuren.sql) by copying them
> as working files species_master.sql and
> tervuren_target.sql. It is with these that I will
> (somehow) copy all wood species names that are NOT in the
> species_master table that
> are, however, in the tervuren_target file. (Later after
> species_master is expanded, I can
> rename it back again to 'species').
>
> - The key column for comparison in both files is called
> 'species_name' in both files.
>
> - I have already treated the tervuren_target table so that
> there are no duplicate records
> within itself with respect to column species_name.
>
> - There are a couple columns I do not have to carry over to
> the species_master table from
> the tervuren_target table. For the columns that do have
> to, I made sure
> they are named exactly the same in both files and that
> the collation is the same in both.
>
> - The field names common to both the source for my data (ie.
> tervuren_target) and for
> the destination for the new data (species_master) are:
>
> genus_name, species_name, authorities_species, source
>
> - The species_master table has about 18 total fields but
> except for the ones above, none of the
> other fields I have not listed will receive any new data
> whatsoever. They are (so to speak) just
> along for the ... ride. All new data will go into the 4
> fields listed above exclusively.
>
> - In any attempt to avoid new duplicates forming as data
> comes over into species_master,
> at NO TIME WHATSOEVER can we allow any records in
> species_master to be deleted!
> The reason is because meaningful data in the other fields
> in species_master would be
> lost and data integrity would be violated.
>
> Just adding ALL records from tervuren_ target was easy but it sure
> did not give me the result i need. Both
> the tervuren_target table and the species_master table have wood
> names in them that are common to each.
> As copying records over is happening, great care has to be taken to
> avoid NEW duplicates forming via
> the combining of records from both tables. In fact, just merging all
> files from tervuren_target over into
> species_master created over 3,000 NEW DUPLICATES! The result was an
> unusable mess!
>
> ===> .... and that is where all the problem is that has stopped me
> from being successful!
>
> I am hoping that an SQL statement would do all the work fine .... but
> if that will not work, I am also open to
> adding any PHP lines for more versatility if needed.
>
> I have made a few column name changes since approaching the group
> back around May 10 but other than that,
> I tried out a script tonight suggested to me back then:
>
> SELECT DISTINCT tervuren_target.genus_name,
> tervuren_target.species_name, tervuren_target.authorities_species,
> tervuren_target.source
> FROM tervuren_target
> LEFT JOIN species_master
> ON tervuren_target.species_name = species_master.species_name
> LIMIT 0,12000
>
> I had to add in the LIMIT statement or for some reason the server
> would time out every time. The script ran ok
> but when I looked at the number of records it created (11,377) that
> was EXACTLY how many records
> there are in the original Tervuren file! In other words, there was no
> success in avoiding new duplicates at all
> and the data did NOT actually merge into species_master .
> I also predicted that the DISTINCT
> statement would only work on the tervuren_target table (which was
> already cleaned of duplicates within itself)
> and I believe I was right. it seems to be extraneous and not the
> needed answer.
>
> .... I had never thought it should be that hard just to add in new
> data to a table of central important without
> introducing new duplicate records --- but it is.
>
> Therefore I come in humble request to all of you, hoping someone can
> figure out what actually will work.
> The solution is harder than may first seem. My email address if you
> need it is billmudry at rogers.com
>
> With thanks in advance,
>
> Fingers crossed, toes crossed, eyeballs crossed .......
>
> Bill Mudry
> Mississauga, Ontario Canada.

This would be much better taken to the DB list, where the entire
audience is knowledgable about databases, so answers are more likely ;-)

First, what database engine are you using? All engines have specific
idiosyncrasies that need to be accounted for, and knowing the engine
will allow more specific answers to your problems.

--
Niel Archer



--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php