MySQL: How to copy table A's data to new non-existing table B?

Question

Update: In similar questions in SO,the table in which data was to be copied was already created unlike this case.

I have table A with few records and want its data copied to another non-existing table B. Looking for some query.

Answer 1

I don't like using CREATE TABLE B AS SELECT * FROM A because that won't capture:

• indexes
• constraints like PRIMARY KEY, UNIQUE KEY, or FOREIGN KEY
• table options like ROW_FORMAT

At least it captures column-level options like NOT NULL and DEFAULT.

mysql> create table A ( i int primary key, x int default 123, unique key (x)) row_format=compressed;

mysql> create table b1 as select * from A;

mysql> show create table b1\G
*************************** 1. row ***************************
       Table: b1
Create Table: CREATE TABLE `b1` (
  `i` int(11) NOT NULL,
  `x` int(11) DEFAULT '123'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

It did get the ENGINE and DEFAULT CHARSET table options, only because those are the global defaults. If I had non-default choices for either of those table options, they'd be lost in the clone table.

Try doing it in two steps like this:

CREATE TABLE B LIKE A;
INSERT INTO B SELECT * FROM A;

The table B will have truly identical definition as table A, including indexes, constraints, and table options.

mysql> create table b2 like A;

mysql> show create table b2\G
*************************** 1. row ***************************
       Table: b2
Create Table: CREATE TABLE `b2` (
  `i` int(11) NOT NULL,
  `x` int(11) DEFAULT '123',
  PRIMARY KEY (`i`),
  UNIQUE KEY `x` (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED

Answer 2

A quick way to create a table as well as copy data in it in a single SQL query.

CREATE TABLE B AS SELECT * FROM A;

Working Example:

http://sqlfiddle.com/#!9/da26e1/1