I am producing a MySQL database that will store results for a particular sport. I have a table containing people, (PERSONID, FIRSTNAME, LASTNAME), another table containing animals names (ANIMALID, ANIMALNAME).
Within the results table, I have the position, personid, animalid, and time. I want to create a query that will insert into the results table a large number of 'results'.
I have managed to create a simple query to look up a persons ID based on their firstname and last name, however I want to take this further and say 'If this person doesn't exist based on their firstname and lastname, insert this into the persons table, and then use their ID for the results'. The same will be the case for the animals.
Also, as a side note, would it be possible to create a sort of procedure that will run through a list of peoples names and animal, and insert it into the results table? (preferable with the above 'if not exist, then insert and use id' scheme)
Thanks,
Graham
Best Answer
Assuming you have some tables for Persons, Animals:
and results:
I suggest you first bulk load the data (possibly with
LOAD DATA
from.txt
or.csv
files) in a table in MySQL (supplying race IDS. If you can't supply raceIDs but you have race names, the tables should be adjusted accordingly). You should have aRace
table as well, this is just a sample procedure:Then you can manipulate them and insert them into the 2-3 tables. For
Person
:Similar for
Animal
:And then in
Result
:If the importing results are satisfying, then you can empty the
BulkData
table and repeat the procedure with more files. TheNOT EXISTS
conditions will take care and not allow duplicates even if you try to load same data twice.