Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 75 ยท Tables, Constraints and Schema Design

Copying, Cloning and Truncating Tables

Copy and reset MySQL tables with CREATE TABLE LIKE, AS SELECT and INSERT SELECT, empty them with TRUNCATE and swap tables atomically with RENAME TABLE.

Real databases are constantly rearranged: you copy a table before a risky change, build a rebuilt version beside the live one, archive last year's rows, or wipe a staging table between test runs. MySQL gives you a handful of statements for these jobs, and each does a slightly different part of the work: the definition, the rows, or the name.

This lesson puts them side by side: CREATE TABLE ... LIKE, CREATE TABLE ... AS SELECT, INSERT ... SELECT, TRUNCATE, DROP, and the star of safe maintenance, RENAME TABLE, which swaps tables atomically. The earlier CREATE TABLE lesson introduced LIKE and AS SELECT in a few lines, and DELETE versus TRUNCATE compared the two ways to empty a table. Here they are used together in real workflows. MySQL-only statements are read-only; the swap pattern runs live in SQLite.

Which tool copies what

Copy the shape, the rows, or both

CREATE TABLE LIKE Columns, indexes, defaults. No rows.
AS SELECT Rows and column types. No keys.
INSERT SELECT Rows into an existing table.

Combine LIKE with INSERT SELECT for a full copy.

1 / 2

CREATE TABLE ... LIKE

LIKE clones the definition of a table: columns with their types, nullability and defaults, AUTO_INCREMENT settings, primary key, indexes (including unique and fulltext), CHECK constraints (8.0.16+), and storage engine and charset options. It copies no rows and no foreign keys, and the new table's AUTO_INCREMENT counter starts at 1.

SQL
CREATE TABLE orders_archive LIKE orders;

Because the copy has the same indexes as the original, it is the right starting point for an archive table, a scratch copy for a migration test, or the "new" table in a rebuild. You can add or drop indexes on the copy before loading it, which is much faster than building them after millions of rows are in.

You can name the other database too: CREATE TABLE archive.orders_2024 LIKE shop.orders;. The privilege rules apply to both.

Filling the copy: INSERT ... SELECT

Rows are copied with INSERT ... SELECT, which can filter, reorder and transform:

SQL
INSERT INTO orders_archive
SELECT * FROM orders WHERE placed_at < '2025-01-01';

SELECT * depends on the two tables having the same column order, which LIKE guarantees, but a safer habit is to list columns explicitly: INSERT INTO t (a, b, c) SELECT a, b, c FROM s. For a big table, copy in batches by primary key range so each statement is a short transaction, which avoids long locks, a huge undo log and replication lag:

SQL
INSERT INTO orders_archive
SELECT * FROM orders WHERE id BETWEEN 1 AND 50000 AND placed_at < '2025-01-01';
-- next batch: 50001 to 100000, and so on

CREATE TABLE ... AS SELECT

CREATE TABLE ... SELECT (CTAS) creates the table from the query and fills it in one statement:

SQL
CREATE TABLE paid_orders AS
SELECT id, customer_id, total FROM orders WHERE status = 'paid';

The new table gets the query's column names and types, but no primary key, indexes or foreign keys, and AUTO_INCREMENT is not carried. Two refinements make the result better:

  1. Declare the columns and keys yourself, then let the SELECT fill them. The query's columns are matched by name, and extra declared columns are allowed:
SQL
CREATE TABLE paid_orders (
  id          INT UNSIGNED NOT NULL PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  total       DECIMAL(10,2) NOT NULL,
  KEY idx_customer (customer_id)
)
SELECT id, customer_id, total FROM orders WHERE status = 'paid';
  1. Or just add keys afterwards with ALTER TABLE paid_orders ADD PRIMARY KEY (id);, understanding that this rebuilds the table.

In MySQL 8.0.21 and later, CREATE TABLE ... SELECT is atomic on InnoDB: if the statement fails halfway, no table is left behind. In older versions you could be left with a partly filled table.

Which one to use? Prefer LIKE plus INSERT ... SELECT when you want an exact structural clone, and CTAS when the new table is a different shape (aggregates, joins, renamed columns) and you will add keys yourself.

Copying the live shape: SHOW CREATE TABLE

To see exactly what you would clone, run SHOW CREATE TABLE orders\G. It prints the full CREATE TABLE, including defaults MySQL filled in, foreign keys and options. Use it when you need to copy a table with its foreign keys, since LIKE omits them: copy the text, rename the table, and run it. It is also the best check that a copy really matches the original.

TRUNCATE TABLE

TRUNCATE TABLE t empties a table quickly by dropping and recreating it internally. The main differences from DELETE FROM t, in short:

  • it can't be rolled back (it is DDL and commits implicitly),
  • it resets the AUTO_INCREMENT counter to 1,
  • it doesn't fire DELETE triggers and reports 0 rows affected,
  • it is refused (error 1701) if another table's foreign key references it, even when that table is empty.

For clearing a staging table before each load it is ideal. To keep the counter, use DELETE. If you must truncate a parent table, drop the child's foreign key first, or temporarily disable checks in a controlled maintenance window with SET FOREIGN_KEY_CHECKS = 0; (and set it back to 1 afterwards).

RENAME TABLE: the atomic swap

RENAME TABLE changes names, and it can do several renames in one atomic statement. During the statement, all affected tables are locked, and other sessions see either the old arrangement or the new one, never a moment where the table is missing:

SQL
RENAME TABLE orders TO orders_old,
             orders_new TO orders;

This is the standard technique for rebuilding or cleaning a table with minimal downtime:

  1. CREATE TABLE orders_new LIKE orders;
  2. Change the new table if needed (add an index, change a column) while it is empty, which is cheap.
  3. Copy the rows you want to keep with INSERT ... SELECT, in batches.
  4. Catch up on rows written to orders since you started.
  5. RENAME TABLE orders TO orders_old, orders_new TO orders; in one statement.
  6. Verify, then DROP TABLE orders_old; later.

Two things to remember. A plain two-statement rename (RENAME TABLE orders TO orders_old; RENAME TABLE orders_new TO orders;) leaves a gap in between where orders doesn't exist and queries fail, so always use one statement. And RENAME TABLE can move a table to another database (RENAME TABLE a.t TO b.t) as long as it is on the same filesystem, which is an instant way to archive. Tools such as gh-ost and pt-online-schema-change automate this swap with triggers or the binary log so the copy stays in sync.

The same swap, live

SQLite has no multi-table RENAME TABLE, but its DDL is transactional, so the same safe swap works inside a transaction. The runner below rebuilds books without out-of-print titles:

SQL Build a new table, then swap names

Press Run (or Ctrl + Enter) to see the result here.

The live table now holds two rows, the old copy still held all four until it was dropped, and nobody could have observed a moment without a books table. In MySQL the two renames collapse into one RENAME TABLE statement, because MySQL DDL cannot be rolled back.

Dropping safely

  • DROP TABLE IF EXISTS t; avoids an error when a script runs twice.
  • Dropping a table referenced by a foreign key is refused until the constraint is removed.
  • DROP TABLE is not recoverable without a backup. A good habit is to rename first (RENAME TABLE t TO t_dropme_2025_11) and drop it after a week with no complaints.
  • Dropping a large table can briefly stall a busy server while InnoDB frees pages. Do it off-peak.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does CREATE TABLE copy LIKE original copy?

    LIKE clones columns, defaults, indexes and options. It does not copy rows or foreign keys.
  2. 2. Which statement leaves no moment where the table name is missing?

    A multi-table RENAME TABLE is atomic: other sessions see the old names or the new ones, never a gap.
  3. 3. Why might TRUNCATE TABLE fail on a table that is already empty?

    MySQL refuses TRUNCATE on a table referenced by a foreign key (error 1701), whether or not it holds rows.
  4. 4. Which CREATE TABLE ... AS SELECT fact is true?

    CTAS builds columns from the query result. You must add keys and indexes yourself, or declare the table up front.
  5. 5. Why copy a big table in batches by primary key range?

    Batches keep each transaction small, which keeps the live system responsive and replicas up to date.

Summary

  • CREATE TABLE ... LIKE clones structure and indexes without rows or foreign keys; INSERT ... SELECT fills it, ideally in batches.
  • CREATE TABLE ... AS SELECT copies rows and column types only; declare keys up front or add them afterwards.
  • SHOW CREATE TABLE is the way to copy a table together with its foreign keys.
  • TRUNCATE is the fast, non-transactional reset that resets AUTO_INCREMENT and is blocked by referencing foreign keys.
  • A single multi-table RENAME TABLE swaps a rebuilt table into place atomically; rename before you drop.

Next: Temporary Tables and Derived Data, for tables that vanish on their own.