May 16, 2021 SQL
In some cases, you may need to copy a data table as is. However, CREATE TABLE does not meet your needs because the replication table must have the same indexes, default values, and so on as the original table.
If you're using a MySQL related database management system, here are a few steps you can take to solve the problem:
Try the following example to create a TUTORIALS_TBL table for the table, which looks like this:
Get the full structure of the data table:
SQL> SHOW CREATE TABLE TUTORIALS_TBL \G;
*************************** 1. row ***************************
Table: TUTORIALS_TBL
Create Table: CREATE TABLE `TUTORIALS_TBL` (
`tutorial_id` int(11) NOT NULL auto_increment,
`tutorial_title` varchar(100) NOT NULL default '',
`tutorial_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY (`tutorial_id`),
UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
) TYPE=MyISAM
1 row in set (0.00 sec)
Change the table name to create a new table:
SQL> CREATE TABLE `CLONE_TBL` (
-> `tutorial_id` int(11) NOT NULL auto_increment,
-> `tutorial_title` varchar(100) NOT NULL default '',
-> `tutorial_author` varchar(40) NOT NULL default '',
-> `submission_date` date default NULL,
-> PRIMARY KEY (`tutorial_id`),
-> UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected (1.80 sec)
After step 2, the database will have a clone table. I f you also want to copy the data from the old table, you can perform INSERT INTO... SELECT statement.
SQL> INSERT INTO CLONE_TBL (tutorial_id,
-> tutorial_title,
-> tutorial_author,
-> submission_date)
-> SELECT tutorial_id,tutorial_title,
-> tutorial_author,submission_date,
-> FROM TUTORIALS_TBL;
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
Eventually, you will have an identical clone table as scheduled.