1

I have already known that here is question like mine:

PHP/MySQL: Copy Table and Data from one Database to another

But, that question was asked about 4 years ago, so I would like to repeat it today.

    INSERT INTO `wp_estate`.`wp_posts`(post_title)
    SELECT `s_description`  FROM      `u519633785_armar`.`oc_t_item_description`
    WHERE `fk_i_item_id` > 4;

How I can "see" two databases at the same time

Community
  • 1
  • 1

1 Answers1

1

You first have to create a table into database you want to copy the data.

To get the complete CREATE statement of table. Run Following query in your DB:

show create table table_name;

This will return you the complete create query of table.

After creating a table to your new database, you can copy all data from your old table to table of your new DB.

Query to copy data:

insert into newDB.your_table select * from oldDB.your_table;

Updated insert query:

INSERT INTO `wp_estate`.`wp_posts`(post_title)
SELECT `s_description`  FROM `u519633785_armar`.`oc_t_item_description`
WHERE `fk_i_item_id` > 4;
Abhishek Ginani
  • 4,511
  • 4
  • 23
  • 35
  • At first, respose of MySQL server is: _#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wp_posts .' at line 1_ At second, tables has a diffent format, because first table comes from osclass cms and the second — from Wordpess. I need to convert data from one MySQL DB to Wordpess database. –  Nov 08 '15 at 15:30
  • Hi, I have updated `show create table` query , check the updated one. you get that error because I have typo there in query. and if you have different column in you new table then you have to specify the table columns in insert query also to insert the data. – Abhishek Ginani Nov 08 '15 at 15:34
  • Mistake is in a **" . "** , you query works only without it, mate. Because this dot is the end of sentence :-) –  Nov 08 '15 at 15:43
  • What query you are executing.please share. – Abhishek Ginani Nov 08 '15 at 15:45
  • OK, I've understood your approach to the problem. You suggest to create copy of table from DB1 in DB2. Then copy needed content from "cloned" table to the final destination. I guess so... –  Nov 08 '15 at 15:50
  • Yeah..but as you are saying that you have different fields in table. You can directly insert data into that table,if you have already created that. – Abhishek Ginani Nov 08 '15 at 15:52
  • Right, delimiter is semicolon, so everything is OK! I think there was a lag of some kind: I see correct version of the query only after I made second refresh of the webpage –  Nov 08 '15 at 15:56
  • Anyhow, I can do this operation within **ONE** database only? As far as I understand the main point of our conversation. –  Nov 08 '15 at 16:02
  • No you can query tables of other database,if they are on same MySQL server. if your both databases created on different MySQL servers then these queries will not work. – Abhishek Ginani Nov 08 '15 at 16:06
  • If I try to do so with qurery from my OP, I have an error: _#1046 - No database selected_ Why so?! –  Nov 08 '15 at 16:10
  • I have updated your query you have typo in `Backticks`. Check that once. – Abhishek Ginani Nov 08 '15 at 16:14