how to append the rows of one table into another having the same schema and data type

Question

There is a table A in one database Q and table A in database R. How to add the rows of table R.A at the end of table Q.A.

Problem is both the tables are having same primary key 'id' (auto increment), so if table Q.A has 10 rows(id 1 to 10) then after adding the content of table R.A having 5 rows(id 1 to 5) into Q.A, then at the end Q.A will be having 15 rows (id 1 to 15)

Thanks.

Answer 1

If both of the tables have same structures, then you can adopt following procedures to copy rows from one database table to the other.

1. Create a temporary table on R.A table.
2. Drop primary key column from the temporary table.

3. Now copy all rows from temporary table to Q.A table, as below:

   INSERT INTO q.a SELECT 0, * FROM temporary_table

4. Now, drop the temporary table.

Example:

CREATE TABLE tmp SELECT * from R.A WHERE ...;
ALTER TABLE tmp drop pk_id; -- drop auto increment field
INSERT INTO Q.A SELECT 0, tmp.* FROM tmp;
DROP TABLE tmp;

This way, old primary key values copied from other database table are not used but auto generated on when used '0' in SELECT statement.

Make sure that user has access rights on other database table.

Also refer to:

• Mysql: Copy row but with new id