Solved

LOAD DATA INFILE into multiple tables with one statement

Posted on 2004-08-29
4
1,430 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MYSQL simple update statement 3 54
xampp tool 12 50
mcrypt_create_iv() is deprecated 4 160
Mysql Left Join Case 10 55
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

777 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