Select large amount of ID's that does not exist on the other table?

[js_11]

Hey,
I have two tables and I need to add id-numbers from table B to table A, but only those id's that does not already exist in table A.

I was thinking to first check every id from table A and make a comma separated list of it, and then SELECT from table B like: SELECT id FROM b where id NOT IN(large_list_of_ids). And after that insert those id's to table A.

But this does not seem to be very efficient way. Are there any tips or tricks how this should be done?

And please note that I could just keep an record of autoincrement number from table B, and always select and transfer those id's that came after last "autoincrement save point", but this is not fool proof enough. I have to check the whole B table and make sure all id's will get copied to table A that does not exist in table A.

Any sugestions?

And I forgot to mention, I'm using MYSQL.

[guido2004]

INSERT INTO a
 
SELECT id 
FROM b 
WHERE id NOT IN (SELECT id FROM a)

[js_11]

And oh heck, those tables A and B are on different servers. That INSERT INTO a SELECT.. wouldn't work in that case.

[r937]

And I forgot to mention, I'm using MYSQL.

1. okay, so please post in the mysql forum in future

2. try INSERT IGNORE

[js_11]

Yeah I should have posted to the mysql forum, but at the time of writing, I was too confused thinking about how to explain my problem clear enough, slipped into a wrong area

But thanks, I'll give it a try. Just plain simple select all id's from table B and then INSERT IGNORE to table A.