MYSQL Replace entire table with another one

Question

I am trying to achieve this. Say I have a two databases - db1 , db2. They each have a table called tb , the table structure is the same for both however, the records are different. Is there any elegant way I can replace all records inside db2.tb with the records from db1.tb. I think I can achieve this with php , but I`m looking for an elegant way.

Answer 1

You can TRUNCATE (if you want to remove all existing data in db2), then INSERT:

TRUNCATE db2.tb;
INSERT INTO db2.tb SELECT * FROM db1.tb;

Answer 2

You can use sql to truncate one table and then insert the data from the other table into it.

Put it into a transaction to keep it safe. http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-transactions.html

TRUNCATE test.stuff;
INSERT INTO test.stuff SELECT * FROM testdb.stuff;

Answer 3

You can drop and create via select

 DROP Table  db2.your_table ;

 Create table db2.your_table
 select * from db1.your_table;