Mass insert problems

bkschatz

I am trying import from a comma delimited file to MySQL. I wrote a program that will make an insert statement for every record, but when you are trying to run 70,000 records it just times out. Is there a way that I can do this without trying to execute an insert statement for every single record. I have been messing with this but I keep getting a syntax error.

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\' ]
]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name,...)]

I got this right off of MySQL.com, and made these changes

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'APPLICATION.txt'
[REPLACE | IGNORE]
INTO TABLE application
[FIELDS
[TERMINATED BY ',']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\' ]
]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name,...)]

MySQL said:

You have an error in your SQL syntax near '[LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'APPLICATION.txt'
[REPLACE | IGN' at line 1

Any help would be great. I don't know if I just need to weed out some of the extra junk or what. Thanks a lot,

Brandon Schatz

prycejones

I remember once I read about maximum_buffer_size, your problem doesnt have anything to do with that does it?

[deleted]

The timeout is PHP coming to it's 30 second default limit, use set_time_limit() to overcome that.

Second, what you copied from the mysql manual is the SYNTAX DESCRIPTION of the LOAD INFILE command. The items between brackets [] are optional extras, look at some examples (use google)

A forum, a FAQ, email notification, what else do you need?