CODEWITHSUNDEEP
mysql
Abhishek Tripathi
Abhishek Tripathi

Reputation: 1600

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

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.

Upvotes: 1

Views: 3506

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 23992

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:

Upvotes: 2

Related Questions