I am doing some databese thing, I need copy one table from one model to another, but i try many ways there no effect.
Is there any way for doing this?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
If you just want to do a single table through the MySQL Workbench.
In MySQL Workbench:
- Connect to a MySQL Server
- Expand a Database
- Right Click on a table
- Select Copy To Clipboard
- Select Create Statement
A create statement for the table will be copied to your clipboard similar to the below:
CREATE TABLE `cache` ( `cid` varchar(255) NOT NULL DEFAULT '', `data` longblob, `expire` int(11) NOT NULL DEFAULT '0', `created` int(11) NOT NULL DEFAULT '0', `headers` text, `serialized` smallint(6) NOT NULL DEFAULT '0', PRIMARY KEY (`cid`), KEY `expire` (`expire`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Create the table in the new database
- Open a new SQL tab for executing queries (File->New Query Tab)
Alter the create table code to include the database to create the table on.
CREATE TABLE `databaseName`.`cache` ( `cid` varchar(255) NOT NULL DEFAULT '', `data` longblob, `expire` int(11) NOT NULL DEFAULT '0', `created` int(11) NOT NULL DEFAULT '0', `headers` text, `serialized` smallint(6) NOT NULL DEFAULT '0', PRIMARY KEY (`cid`), KEY `expire` (`expire`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Then click the Execute button (looks like a lightening Bolt)
That will copy the table schema from one db to another using the MySQL workbench. Just refresh the tables in the database and you should see your newly added table
Your best option is probably to create a stripped down version of the model that contains the objects you want to carry over. Then open the target model and run
File -> Include Model.... Select the stripped down source model and there you go.
- Select tab with source database
- In menu: Server->Data Export
- Select Schema and the Table as Schema Object
- Select option Export to Self-Contained File and check Create Dump in a Single Transaction (self-contained only)
- Copy full file path to clipboard
- Start Export
- Select tab with target database
- In menu: Server->Data Import. Make sure your target database name is at the top left corner of the Data Import view
- Select Import from self contained file and paste full file path from clipboard
- Select Default Target Schema
- Select Dump Content (Dump Structure and Data etc…)
- Start Import
You can just use a select statement. Here I am creating a duplicate of “original_table” table from the “original_schema” schema/database to the “new_schema” schema :
CREATE TABLE new_schema.duplicate_table AS Select * from original_schema.original_table;
You can just put any select statement you need ,add a condition and select the columns :
CREATE TABLE new_schema.duplicate_table AS SELECT column1, column2 FROM original_schema.original_table WHERE column2 < 11000000;
I think it is worth mentioning that
- a copied table may reference fields in tables of the original schema, that do not exist, in the schema where it’s to be copied. It might be a good idea, to inspect the table for these discrepancies, before adding it to the other schema.
- it’s probably a good idea, to check engine compatibility (e.g. InnoDB vs MyISAM) and character set.
step 1 : Righit click on table > copy to clipboard > create statement
step 2: paste clipboard in the query field of workbench.
step 3: remove (“) from the name of the table and name of the model(schema)followed by a dot.
eg : `cusine_menus` -> schema_name.cusine_menus
If you already have your table created and just want to copy the data, I’d recommend using the “Export Data Wizard” and “Import Data Wizard”. It is basically choosing stuff in the program for exporting and then importing the data and is easy to use.
MySQL has an article on the wizards here: Table Data Export and Import Wizard
To copy data using the wizards, do the following:
- Find the table in the list from which you want to copy data from.
- Right click and choose “Table Data Export Wizard.”
- Choose the columns you wish to copy.
- Choose a location to save a *.csv or *.json file with the copied data.
- Find the table to insert the copied data to.
- Right click and choose “Table data import wizard”.
- Choose the file you just exported.
- Map the columns from the table you copied from to the table you insert to.
- Press “Finish”. The data is inserted as you chose.
create table .m_property_nature like .m_property_nature;
INSERT INTO .m_property_nature SELECT * from .m_property_nature;
You can get the crate table query from table info and use the same query on different database instance.
- show create table TABLENAME.content and copy the query;
- Run the generated query on another Db instance connected.
In this post, we are going to show you how to copy a table in MySQL
First, this query will copy the data and structure, but the indexes are not included:
CREATE TABLE new_table SELECT * FROM old_table;
Second, this query will copy the table structure and indexes, but not data:
CREATE TABLE new_table LIKE old_table;
So, to copy everything, including database objects such as indexes, primary key constraint, foreign key constraints, triggers, etc., run these queries:
CREATE TABLE new_table LIKE old_table; INSERT new_table SELECT * FROM old_table;
If you want to copy a table from one database to another database:
CREATE TABLE destination_db.new_table LIKE source_db.old_table; INSERT destination_db.new_table SELECT * FROM source_db.old_table;