simple LOAD DATA INFILE question

pseudocool

I did some research and figured out the best way to accomplish inserting large amounts of data is to use:

LOAD DATA INFILE 'thefile.txt' INTO TABLE the_table (col1);

But, I cant figure out for the life of me how to structure my query so it only loads into only those rows where there is a certain criteria, pseudo code example :

If the value of 'col2' is equal to '20'
    LOAD DATA INFILE 'thefile.txt' INTO TABLE the_table (col1)
else
      Do nothing

in other words, could a simple if statement or even a while loop work for the application? i will be populating the DB with the same data in some instances is why i ask...

johanafm

I havn't tried using control flow functions with load data infile, so I can't say for sure if this will work or not, but it would surprise me if it doens't. It is also very easy to test.

If it does work, and assuming you have a primary key or other unique index, you can use key collisions with the IGNORE keyword to exclude anything you want, by using INTO TABLE (@var1, @var2,...) so that you use one variable per table column, use CASE WHEN to check for @var2!=20, then SET primary_key_field = 'existing value', and otherwise SET field1=@var1, field2=@var2.

Another way to do this would be to parse the file using whatever programming language you prefer, and create a new file containing only rows where col2=='20' and use that with load data infile.

pseudocool

johanafm;10986654 wrote:
I havn't tried using control flow functions with load data infile, so I can't say for sure if this will work or not, but it would surprise me if it doens't. It is also very easy to test.

If it does work, and assuming you have a primary key or other unique index, you can use key collisions with the IGNORE keyword to exclude anything you want, by using INTO TABLE (@var1, @var2,...) so that you use one variable per table column, use CASE WHEN to check for @var2!=20, then SET primary_key_field = 'existing value', and otherwise SET field1=@var1, field2=@var2.

Another way to do this would be to parse the file using whatever programming language you prefer, and create a new file containing only rows where col2=='20' and use that with load data infile.

Thanks for the in depth reply, my only issue is i am a bit new to this, my language of choice is PHP...however i am pretty new to both PHP and MySQL. could you possibly explain a little simpler terms for me or perhaps show an example code snip for me? It would help to see it in practice.

johanafm

Well, I tried and unfortunately it didn't work, so it would seem that you're not allowed to use control flow functions with load data infile. In case the error is on my side, here's the code representing my idea for reference

LOAD DATA INFILE 'the_file.txt' IGNORE INTO TABLE the_table (@id, @col2, @col3) SET
CASE WHEN @co2 != 20
	THEN id=1
	ELSE id=@id
END CASE, col2=@col2, col3=@col3

Failing that approach, I would instead go with a temporary table into which you load all data, and then followup with a secondary query to perform the insert for matching rows

-- Create a temporary table with structure identical to the_table
CREATE TEMPORARY TABLE tmp_table LIKE the_table;
-- in case col2 isn't indexed in the_table, add index for col2
ALTER TABLE tmp_table ADD INDEX (col2);

-- Load data into temp table
LOAD DATA INFILE 'testdata.txt' INTO TABLE temp_table;

-- Perform insert for rows where col2=20
INSERT INTO the_table SELECT * FROM temp_table WHERE col2=20;

-- Get rid of the temp_table (otherwise done automatically when you close the connection)
DROP TABLE temp_table