Easiest way to copy a table from one database to another

Question

What is the best method to copy the data from a table in one database to a table in another database when the databases are under different users   I know that I can use  INSERT INTO database2 table2 SELECT   from database1 table1   But here the problem is that both database1 and database2 are under different MySQL users  So user1 can access database1 only and user2 can access database2 only  Any idea

User · Answer

it s worked good for me CREATE TABLE dbto table name like dbfrom table name  insert into  dbto table name select   from dbfrom table name

User · Answer

create table destination customer like sakila customer Database name tablename   this will only copy the structure of the source table  for data also to get copied with the structure do this create table destination customer as select   from sakila customer

User · Answer

MySql Workbench  Strongly Recommended    This will easily handle migration problems  You can migrate selected tables of selected databases between MySql and SqlServer  You should give it a try definitely

User · Answer

Here is another easy  way    use DB1    show create table TB1      copy the syntax here in clipboard to create TB1 in DB2  use DB2      paste the syntax here to create the table TB1    INSERT INTO DB2 TB1  SELECT   from DB1 TB1

User · Answer

Try mysqldbcopy  documentation   Or you can create a  federated table  on your target host  Federated tables allow you to see a table from a different database server as if it was a local one   documentation   After creating the federated table  you can copy data with the usual insert into TARGET select   from SOURCE

User · Answer

If you are using PHPMyAdmin  it could be really simple  Suppose you have following databases   DB1  amp  DB2   DB1 have a table users which you like to copy to DB2  Under PHPMyAdmin  open DB1  then go to users table   On this page  click on the  Operations  tab on the top right  Under Operations  look for section Copy table to  database table     amp  you are done

User · Answer

If your tables are on the same mysql server you can run the following  CREATE TABLE destination db my table SELECT   FROM source db my table  ALTER TABLE destination db my table ADD PRIMARY KEY  id    ALTER TABLE destination db my table MODIFY COLUMN id INT AUTO INCREMENT

User · Answer

Is this something you need to do regularly  or just a one off   You can do an export  eg using phpMyAdmin or similar  that will script out your table and its contents to a text file  then you could re-import that into the other Database

User · Answer

One simple way to get all the queries you need is to use the data from information schema and concat   SELECT concat  CREATE TABLE new db    TABLE NAME    LIKE old db    TABLE NAME       FROM  TABLES  WHERE TABLE SCHEMA    old db     You ll then get a list of results that looks like this   CREATE TABLE new db articles LIKE old db articles  CREATE TABLE new db categories LIKE old db categories  CREATE TABLE new db users LIKE old db users        You can then just run those queries   However it won t work with MySQL Views  You can avoid them by appending AND TABLE TYPE    BASE TABLE  from the initial query

User · Answer

First create the dump  Added the --no-create-info --no-create-db flags if table2 already exists  mysqldump -u user1 -p database1 table1  gt  dump sql  Then enter user1 password  Then  sed -e  s  table1   table2    dump sql  mysql -u user2 -p database2  lt  dump sql  Then enter user2 password  Same as  helmors answer but the approach is more secure as passwords aren t exposed in raw text to the console  reverse-i-search  password sniffers  etc   Other approach is fine if it s executed from a script file with appropriate restrictions placed on it s permissions

User · Answer

Use MySql Workbench s Export and Import functionality  Steps   1  Select the values you want    E g  select   from table1      Click on the Export button and save it as CSV  create a new table using similar columns as the first one    E g  create table table2 like table1    select all from the new table    E g  select   from table2     Click on Import and select the CSV file you exported in step 2

User · Answer

use below steps to copy and insert some columns from one database table to another database table-   CREATE TABLE tablename   columnname datatype  size   columnname datatype  size      2 INSERT INTO db2 tablename SELECT columnname1 columnname2 FROM db1 tablename

User · Answer

IN xampp just export the required table as a  sql file and then import it to the required

User · Answer

CREATE TABLE db1 table1 SELECT   FROM db2 table1  where db1 is the destination and db2 is the source

User · Answer

With MySQL Workbench you can use Data Export to dump just the table to a local SQL file  Data Only  Structure Only or Structure and Data  and then Data Import to load it into the other DB   You can have multiple connections  different hosts  databases  users  open at the same time

User · Answer

For me I need to specific schema to  quot information schema TABLES quot  for example  SELECT concat  CREATE TABLE new db    TABLE NAME    LIKE old db    TABLE NAME       FROM information schema TABLES  WHERE TABLE SCHEMA    old db

User · Answer

I use Navicat for MySQL      It makes all database manipulation easy    You simply select both databases in Navicat and then use    INSERT INTO Database2 Table1 SELECT   from Database1 Table1

User · Answer

If you have shell access you may use mysqldump to dump the content of database1 table1 and pipe it to mysql to database2  The problem here is that table1 is still table1   mysqldump --user user1 --password password1 database1 table1     mysql --user user2 --password password2 database2   Maybe you need to rename table1 to table2 with another query  On the other way you might use sed to change table1 to table2 between the to pipes   mysqldump --user user1 --password password1 database1 table1     sed -e  s  table1   table2        mysql --user user2 --password password2 database2   If table2 already exists  you might add the parameters to the first mysqldump which dont let create the table-creates   mysqldump --no-create-info --no-create-db --user user1 --password password1 database1 table1     sed -e  s  table1   table2        mysql --user user2 --password password2 database2

User · Answer

I know this is old question  just answering so that anyone who lands here gets a better approach   As of 5 6 10 you can do   CREATE TABLE new tbl LIKE orig tbl    Refer documentation here  https   dev mysql com doc refman 5 7 en create-table-like html

[mysql] Easiest way to copy a table from one database to another?

Examples related to mysql

Examples related to database