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.
-
1Just blindly replace the records, or do some fields determine/allow you to decide which data should be replaced? eg: replacing matching ID's etc... Are there constraints on the tables? Indexes that are likely to require a table rebuild? Define "elegant way"...Elias Van Ootegem– Elias Van Ootegem2016-10-17 15:09:50 +00:00Commented Oct 17, 2016 at 15:09
-
I think OP wants an in db solution rather than do it thru PHP. @nikksan Did you try stackoverflow.com/questions/22912167/…Null Head– Null Head2016-10-17 20:53:26 +00:00Commented Oct 17, 2016 at 20:53
Add a comment
|
3 Answers
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;
1 Comment
easleyfixed
One advantage of this method, is that since the table column structure is the same, the primary key (auto increment etc) should already be set and you wouldn't need to ALTER the table to set that back up.
You can drop and create via select
DROP Table db2.your_table ;
Create table db2.your_table
select * from db1.your_table;
2 Comments
easleyfixed
Don't forget If Exists even though we assume it shold be there.
easleyfixed
And do note, this does not copy the primary key value, you need to do an Alter statement and reset the primary key settings (Auto increment etc)
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;
