Solved

LOAD DATA INFILE into multiple tables with one statement

Posted on 2004-08-29
4
1,379 Views
Last Modified: 2008-01-09
I have a .csv file I need to have loaded into a mySql database version 4.0.18.

The database is rather new, and empty of data as of right now.

I am interested in using the LOAD DATA INFILE command to import the data from the csv file into the database tables.

In the .csv file I have the following information:
Column
Hotel_Name      - Hotel Table
CheckIn_Date      - Reservation Table
CheckOut_Date      - Reservation Table
Room_Type      - Room Table
Room_Rate      - Room Table
Sabre_Code      - Reservation Table
Reservation_Number      - Reservation Table
Cancellation_Number      - Cancellation Table

There are hundreds of rows in the csv file, so it is not feasable to do an insert statement to put each record into the database.

I can't seem to find the correct syntax to insert data into multiple tables from one LOAD DATA INFILE command.  Is this possible?

I have tried to using this, but I get a Syntax error.

LOAD DATA INFILE 'c:/hoteldata.csv' REPLACE
   INTO TABLE hotel
   FIELDS TERMINATED BY ','
        WHEN hotel_name != ''
   ( hotel_name )
   INTO TABLE reservation
   FIELDS TERMINATED BY ','
        WHEN checkin_date != ''
   (  checkin_date )
   INTO TABLE reservation
   FIELDS TERMINATED BY ','
        WHEN checkout_date != ''
   (  checkout_date )
   INTO TABLE room
   FIELDS TERMINATED BY ','
        WHEN room_type != ' '
   (  room_type )
   INTO TABLE room
   FIELDS TERMINATED BY ','
        WHEN room_rate != ' '
   (  room_rate )
   INTO TABLE reservation
   FIELDS TERMINATED BY ','
        WHEN sabre_code != ' '
   (  sabre_code )
   INTO TABLE reservation
   FIELDS TERMINATED BY ','
        WHEN reservation_id != ' '
   (  reservation_id );

I have also tried this, but same result Syntax error:
LOAD DATA INFILE 'c:/hoteldata.csv' INTO TABLE hotel FIELDS TERMINATED BY ','
     TEXT_FIELDS (text_field1, text_field2, text_field3, text_field4, text_field5, text_field6, text_field7)
     SET hotel_name=CONCAT(text_field1)
     IGNORE (text_field2, text_field3, text_field4, text_field5, text_field6, text_field7);

Any ideas on how to handle inserting data into multiple tables from one file, with one statement?
Or am I going to have to do this with a couple of LOAD DATA INFILE statements?

Thanks,
0
Comment
Question by:vinnystp
  • 2
4 Comments
 
LVL 7

Accepted Solution

by:
petoskey-001 earned 500 total points
Comment Utility
You can't load data into multiple tables from one file with one statement using normal MySql tools.  

First load the data into a single table, then move it around to the needed tables using SQL statements.  If you need something more complex some programming will be required.
0
 
LVL 7

Expert Comment

by:petoskey-001
Comment Utility
Oracle can do what you want, but not MySql.  You can use update with multiple tables with MySql 4 or better once your table is imported.
0
 
LVL 1

Expert Comment

by:Parag_Gujarathi
Comment Utility
u can try using the DTS package. using that u may be able to load data into multiple tables.

help is available on the net easily :-)

cheers
Parag.
0
 

Author Comment

by:vinnystp
Comment Utility
Sorry for the delay, been super busy writing all this SQL code to handle this functionality.

Ended up creating a temp table to load the data into, then used Insert/Update statements to update the other tables.

Thanks for the help.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

11 Experts available now in Live!

Get 1:1 Help Now