MySQL: Retrieve large data from table based off a file

Use case: There is a file with millions of account numbers and we want to retrieve name, email and phone numbers for those account numbers.
There are mulitple ways to achive this. We can write a script to loop through each account number from the file, make database queries to get the information, we can also import the file with account numbers into a temporary table, the join this table with other tables to get the information. Here we will go through the steps of the latter method.

1. Create a tmp table.
[code language=”sql”]
CREATE TABLE `tmp_account_numbers` (
`account_number` BIGINT(20) UNSIGNED NOT NULL DEFAULT ‘0’,
PRIMARY KEY (account_number)
);
[/code]

Assume each line in the account file contains an account number, and the file is in /tmp/account_numbers.csv
[code language=”text”]
account_number
111111
222222
333333
444444
555555
666666
777777
888888
999999
100001
100002
[/code]

2. Load account numbers into the tmp table.
[code language=”sql”]
LOAD DATA INFILE "/tmp/account_numbers.csv"
INTO TABLE tmp_account_numbers
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS;
[/code]

3. Export the account_number, name, email, phone into /tmp/customer_contacts.csv
[code language=”sql”]
SELECT tan.account_number, c.name, c.email, c.phone
FROM tmp_account_numbers tan
JOIN customer c ON c.account_number=tan.account_number
INTO OUTFILE "/tmp/customer_contacts.csv"
fields TERMINATED BY ‘,’
LINES TERMINATED BY ‘\r\n’;
[/code]

4. Delete the tmp table.
[code language=”sql”]
drop table tmp_account_numbers;
[/code]