How to Duplicate a Table in MySQL
Last updated July 24, 2026 · By the SaturnSQL team
CREATE TABLE new LIKE old copies the structure including indexes; INSERT INTO new SELECT * FROM old copies the rows. CREATE TABLE ... AS SELECT is shorter but drops indexes and keys.
Full copy with indexes (recommended)
CREATE TABLE ... LIKE reproduces the column definitions, indexes, primary key, and table options, but not foreign key constraints, which you have to add separately. The INSERT then copies every row, so on a large table expect it to take real time and to roughly double the disk used.
CREATE TABLE orders_backup LIKE orders;
INSERT INTO orders_backup SELECT * FROM orders;Quick copy, no indexes
CREATE TABLE ... AS SELECT is one statement, but the result has no indexes, no primary key, and no AUTO_INCREMENT. Column types are inferred from the query and can silently change. It is fine for a throwaway snapshot and a poor choice for anything you intend to query repeatedly or swap back into place.
CREATE TABLE orders_copy AS SELECT * FROM orders;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free