Copying tables in MariaDB and MySQL

There are many reasons why we may want to duplicate a table. We may want to copy the table structure alone, or include the rows, we may only want to copy some sample rows. Let’s see how to copy a table in MariaDB and MySQL.

Copying an empty table

To create an empty copy of a table:

CREATE TABLE table_copy LIKE original_table;

If we want the table to be a bit different from the original table, we can modify it afterwards:

ALTER TABLE table_copy ADD COLUMN qty INT NOT NULL;

Copying table rows

To copy a table and its contents:

CREATE TABLE table_copy AS
    SELECT * FROM original_table;

If we want to be absolutely sure that the table definition is the same, we may prefer this way:

CREATE TABLE table_copy LIKE original_table;
INSERT INTO table_copy SELECT * FROM original_table;

Copying some sample rows

To only copy some sample rows:

INSERT INTO table_copy
    SELECT * FROM original_table LIMIT 1000;

Copying to another database

To copy a table from one database to another:

CREATE TABLE db1.table_copy LIKE db2.original_table;