Solved

LOAD DATA INFILE into multiple tables with one statement

Posted on 2004-08-29
4
1,456 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
ID: 11928536
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
ID: 11928541
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
ID: 11941073
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
ID: 11978387
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

820 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