Finding ID and inserting it into another table

Question

I have a table with two columns. ID and WORD. I've used the following query to insert several files into this table

LOAD DATA LOCAL INFILE 'c:/xad' IGNORE INTO TABLE words LINES TERMINATED BY '\n' (@col1) set word=@col1;

Now I'd like to find specific values and insert them into another table. I know based on this question that I can do the following

insert into tab2 (id_customers, value)
values ((select id from tab1 where customers='john'), 'alfa');

But I'd like to do this based on the files. For example:

Loop through each line of file xad and pass it's value to a query like the following

insert into othertable (word_id)
values ((select id from firsttable where word='VALUE FROM CURRENT LINE OF FILE'));

I can write a Java app to do this line by line but I figured it'd be faster to make MySQL do the work if possible. Is there a way to make MySQL loop over each line, find the ID, and insert it into othertable?

Yes. You can use [INSERT ... SELECT syntax](https://dev.mysql.com/doc/refman/8.0/en/insert-select.html). Make the SELECT a join between your imported table and firsttable. — Bill Karwin, May 17 '21 at 15:43

score 1 · Answer 1 · answered May 17 '21 at 18:34

Plan A: A TRIGGER could be used to conditionally copy the id to another table when encountered in whatever loading process is used (LOAD DATA / INSERT .. SELECT .. / etc).

Plan B: Simply load the table, then copy over the ids that you desire.

Notes:

The syntax for this

insert into tab2 (id_customers, value)
values ((select id from tab1 where customers='john'), 'alfa');

is more like

insert into tab2 (id_customers, value)
    SELECT id, 'alpha'
        FROM tab1
        WHERE customers = 'john'

Finding ID and inserting it into another table

1 Answers1