importing a tab file across multiple tables

Posted on 2011-04-23
Last Modified: 2012-05-11

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.


Question by:andieje
    1 Comment
    LVL 24

    Accepted Solution

    You cannot use load data infile to load multiple tables.  See

    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.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now