CODEWITHSUNDEEP
mysql
user99999
user99999

Reputation: 2024

Insert one column from file, and fill other columns

I have one column in a file line by line, and I want to insert into into the MySQL table.

I also want to ignore rows where the value already exist, and the table has more columns - I want to insert some values into them either.

How can I do that?

I have the following SQL:

LOAD DATA INFILE '/home/sample.txt'
IGNORE INTO TABLE 'table'
CHARACTER SET utf8;

It will ignore existing lines, but I also want to add NOW() as second's column value, and NULL as third's.

How can I do that?

Upvotes: 0

Views: 40

Answers (1)

Arnold Daniels
Arnold Daniels

Reputation: 16553

Use a temporary table.

CREATE TEMPORARY TABLE `table_tmp` (`myfield`);
LOAD DATA INFILE '/home/sample.txt' INTO TABLE `table_tmp` CHARACTER SET utf8;
INSERT IGNORE INTO `table` SELECT `myfield`, NOW(), NULL FROM `table_tmp`;
DROP TEMPORARY TABLE `table_tmp`;

Temporary tables are in memory, so this is pretty fast.

Upvotes: 1

Related Questions