How to use load data Infile to insert into multiple tables?

Question

I use aa python program which inserts many new entries to database, this new entries are spread across multiple tables.

I'm using load data infile to load the file, but this solution is only for one table, and I don't feel like to do this multiple times.

I found http://forge.mysql.com/worklog/task.php?id=875 this but I'm not quite sure if its already implemented or not.

Aaron Murray · Accepted Answer · 2011-05-07 16:15:48Z

3

I am doing exactly what you are trying to do as follows:

Step 1: Create a temp table (holding all the fields of the import file)

Step 2: LOAD DATA LOCAL INFILE -> into the temp table

Step 3: INSERT INTO Table1 ( fieldlist ) SELECT FROM TempTable ( matching fieldlist ) ... include JOINS, WHERE, and ON PRIMARY KEY UPDATE as necessary

Step 4: Repeat step 3 with the second table insert query and so on.

Using this method I am currently importing each of my 22MB data files, and parsing them out to multiple tables (6 tables, including 2 audit/changes tables)

Without knowing your table structure and data file structure it is difficult to give you a more detailed explanation, but I hope this helps get you started

answered May 7, 2011 at 16:15

Aaron Murray

2,0303 gold badges24 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aaron Murray Over a year ago

On a side note, this isn't a 1 pass solution, you will create 1 query (or more) on the temp table for each table you are updating. In my situation I created one for each table, and for my audit tables I created one query for each table column I am auditing. The processing speed on my 22 meg data file is less than 15 seconds to process 30000 records into 6 tables (8 insert queries). I don't know if this is a great speed but in my opinion I am more than happy with that speed.

nabizan · Accepted Answer · 2011-05-07 16:03:42Z

2

load data from local file to insert new data accross multiple tables isnt yet supported (v 5.1)

answered May 7, 2011 at 16:03

nabizan

3,4255 gold badges29 silver badges39 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:02:42Z

0

I don't think LOAD DATA can do that, but why not duplicate the table after importing?

See

Duplicating table in MYSQL without copying one row at a time
Or, if you can go outside mySQL, Easiest way to copy a MySQL database?

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Oct 28, 2010 at 13:18

Pekka

451k150 gold badges990 silver badges1.1k bronze badges

1 Comment

nabizan Over a year ago

those new data im trying to write into mysql table arent already in table ... so duplicating table wont work

Collectives™ on Stack Overflow

How to use load data Infile to insert into multiple tables?

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related