• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

importing a tab file across multiple tables


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.


1 Solution
You cannot use load data infile to load multiple tables.  See http://www.experts-exchange.com/Database/MySQL/Q_21111595.html

You really need to create a script for this - whether Perl, Ruby, some other language.  For things like this I would first load everything into one temporary table then either use SQL statements, a Ruby script (I used to use Perl, but now switched to Ruby) or a MySQL stored procedure.  Since you need id's assigned and remembered, I don't think a series of SQL statements will work for you.  You really need a script.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now