How to Copy a Table Between Databases in MariaDB

How do I copy a table from one MariaDB database to another?

Duplicate a table’s structure and data from one MariaDB database to another with CREATE TABLE … LIKE followed by INSERT INTO … SELECT.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why copy a table between databases in MariaDB?

Developers often need to migrate data from staging to production, seed test environments, or archive historical data. Copying a table keeps column definitions, indexes, and rows intact, saving time compared with manual rebuilds.

What is the basic two-step syntax?

Step 1 clones the table structure with CREATE TABLE target_db.table LIKE source_db.table;. Step 2 moves data with INSERT INTO target_db.table SELECT * FROM source_db.table;. Run both statements in the same session to ensure identical schemas.

How do I copy only the structure?

Run CREATE TABLE target_db.table LIKE source_db.table;. The new table is empty but inherits columns, data types, primary keys, indexes, and constraints—ideal when you plan to load different data later.

How do I copy structure and data in one command?

You can wrap both steps in a single stored procedure or script, but MariaDB requires the INSERT step to move rows. Use START TRANSACTION to keep the operation atomic and avoid partial copies.

Can I filter rows while copying?

Yes. Add a WHERE clause inside the INSERT … SELECT statement. Example: INSERT INTO prod.Orders SELECT * FROM staging.Orders WHERE order_date >= CURDATE() - INTERVAL 30 DAY; to move only recent orders.

How do I preserve AUTO_INCREMENT values?

CREATE TABLE … LIKE keeps AUTO_INCREMENT definitions, and INSERT … SELECT transfers actual id values. The target table’s AUTO_INCREMENT counter automatically advances to the highest inserted id.

Should I lock tables during the copy?

Use LOCK TABLES source_db.table READ, target_db.table WRITE when copying large, live tables to prevent writes that could create inconsistencies.

Best practices for large tables?

Copy during low-traffic windows, copy in batches with LIMIT to reduce lock time, and monitor disk space in the target database. Always test in a non-production environment first.

Why How to Copy a Table Between Databases in MariaDB is important

How to Copy a Table Between Databases in MariaDB Example Usage


--Move last month’s OrderItems from staging to production
CREATE TABLE production.OrderItems LIKE staging.OrderItems;
INSERT INTO production.OrderItems
SELECT *
FROM staging.OrderItems
WHERE order_id IN (
  SELECT id FROM staging.Orders
  WHERE order_date >= CURDATE() - INTERVAL 30 DAY
);
COMMIT;

How to Copy a Table Between Databases in MariaDB Syntax


--Clone structure
CREATE TABLE target_db.Customers LIKE source_db.Customers;

--Copy data (all rows)
INSERT INTO target_db.Customers
SELECT *
FROM source_db.Customers;

--Copy data with filter
INSERT INTO target_db.Orders (id, customer_id, order_date, total_amount)
SELECT id, customer_id, order_date, total_amount
FROM source_db.Orders
WHERE order_date >= CURDATE() - INTERVAL 30 DAY;

Common Mistakes

Running only INSERT … SELECT without CREATE TABLE … LIKE. This fails if the target table does not yet exist. Always create the table first or add CREATE TABLE IF NOT EXISTS.
Copying data without specifying columns when schemas diverged. If source and target structures changed, INSERT … SELECT * causes column mismatch errors. Explicitly list matching columns in both clauses.

Frequently Asked Questions (FAQs)

Does CREATE TABLE … LIKE copy indexes and constraints?

Yes. It duplicates primary keys, unique keys, indexes, AUTO_INCREMENT settings, and column defaults, but not foreign key references to other databases.

Can I copy a table between servers?

Use mysqldump --no-create-db --tables source_db.table | mysql -h target_host target_db, or employ MariaDB replication. The in-database method works only inside the same server.