importing a tab file across multiple tables
Posted on 2011-04-23
Whilst this question make look similar to a recent question I have asked, please ignore the recent question and treat this question independently.
My database contains a M:M relationship between a Feature and an Individual. These features can be analysed by a particular program which yields about 10 values per feature. So the link table is called Feature_Individual which has columns feature_id, individual_id and some other columns to represent the results of that analysis on that feature on a particular individual.
I have an input table which contains the results of said analysis of thousands of features for a specific individual. There is one row in the table per feature. One column has the name of the feature and then the other columns represents all the values associated with the analysis result. There is nothing in the table specifying the individual (though if required it wouldn't be hard to add a column to each row in the file with the individual id). The features however are new and not in the database.
i was wondering if mysql tools could do this automatically. For example, for each row in the input tab flle i would like mysql to create a new feature object for the feature in the feature column. The feature table has an autoinc primary key. (The actual data in the feature column is descriptive so that data would go into another column in the feature table). I want mysql to remember this id and then, along with an individual id i have supplied as a constant value, create a new row in the feature_individual table for each row in the input file and populate the feature_individual fields with columns from the table row.
What is the most efficient way to do this? I don't really want to write a perl script to parse each row and create the appropriate records if i could just manage it with a load data command. I have looked up the docs of the load data command but it doesn't appear to handle anything like this.