mysql merge 2 identical tables? primary key causing error

[iBuddy]

I have two tables test1 and test2

what query do I run if I want to add table 'test2' entries into 'test1' entries?

insert into test1 select * from test2 works however the problem I have is if test2 and test1 have the same primary key id it won't work.

example test2 table
id name
1 nothing

test1 table
1 something

They both have the same primary key "id" so it doesn't work.

I tried:

insert ignore into test1 select * from test2 but it doesn't copy anything....im stumped.

[NogDog]

If you want to add all records from the 2nd table and have new auto-increment IDs assigned to those records, I think you could do something like:

INSERT INTO test1 (col2, col3, col4) SELECT col2, col3, col4 FROM test2

In this case, you need to specify the column list both for the insert and the select, explicitly omitting the auto-increment field in each so that it will be generated automatically.

[yaguar]

You could also take this a step further. Suppose you have MyISAM tables and you had some weak FK dependancy. You could do :

INSERT INTO test1 (col1, col2, col3, col4) SELECT col1+4000000,col2, col3, col4 FROM test2

then when you import the the other table do the same increment. 4,000,000 can be any number as long as it increments higher then max(col1). 🙂