How do I merge two table?

I do have two table each with 50000 data. both tables id start开发者_开发知识库s from 1 - 50000 so when I try to insert to table_1 from table_2 by using

INSERT IGNORE
  INTO table_1 
SELECT *
  FROM table_2

it completely rewrites the existing one. I dont want to rewrite it but add as new so it will totallying 100k. but may say 5 to 10 duplicates in index column so duplicate check on index too. how can I do this??

Use PHP or another favorite language, this will be about 10-20 lines of code...

If table_1 and table_2 need to have different keys first of all for a successfull merge to occur

table_1 keys will have to start from 1-50000 and table_2 key value will have to start from 50001-100000

For duplication you can deal using the on update functionality in mysql

INSERT INTO table_1
    SELECT * FROM table_2
ON DUPLICATE KEY UPDATE table_1.val=table_1.val

If there is an primary key column you have to exclude it from the insert if there is duplicates there.

One solution is to insert with a select that removes duplicate values.

INSERT IGNORE INTO table_1 (f1, f2, f3)
SELECT f1, f2, f2 FROM table_2 WHERE table_2.index NOT IN (SELECT index FROM table_1)

OBS, this is not very efficient on a slow database server and could be written better with more information on the tables.