Solved

LOAD DATA INFILE into multiple tables with one statement

Posted on 2004-08-29
4
1,515 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

696 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