MySQL Load data Statements

Question

i have some large SQL-Files with many "INSERT INTO" Statements.

My Command:

mysql> Load data local infile 'values.sql' into table test_table;

My Statements look like these:

INSERT INTO test_table(column1,column2) VALUES('1','2');
INSERT INTO test_table(column1,column2) VALUES('3','4');

When i run this Command, my test_table column1 will be filled with the complete Statement. Whats my mistake?

djdy · Accepted Answer · 2011-11-29 21:25:31Z

3

load data is for stuff like CSV.

You have actual SQL INSERT statements.

Try:

mysql -u USERNAME -p DATABASENAME < values.sql

answered Nov 29, 2011 at 21:25

djdy

6,9096 gold badges43 silver badges62 bronze badges

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

4 Comments

How slow is slow? How many entries does it do per second? How big is the .sql file?

sql file is 250mb 200 entrys per second? load data just needs 1 or 2 minutes for such a large file. i think i convert them to a csv file.

How long did it take it to run through the whole thing? How many rows did it insert?

Did not test it. There are nearly 3 Million rows in the file. would be way to long. converting to csv and import via load data is much faster

Filip Popović · Accepted Answer · 2011-11-29 21:26:11Z

1

From MySQL reference LOAD DATA INFILE:

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The file name must be given as a literal string.

This means that MySQL expects rows with column values in that file, not SQL statements to execute.

Check Executing SQL Statements to see the way to do what You want.

answered Nov 29, 2011 at 21:26

Filip Popović

2,6553 gold badges19 silver badges18 bronze badges