in Technology

Copying a MySQL table with schema and keys, but not data

Here’s a great thing I learned about MySQL today:

[sql]CREATE TABLE newtable LIKE oldtable[/sql]

Will create a copy of the table along with structure and keys (primary keys, auto increments etc) but not the data.

This is perfect solution to create new tables based on an existing one – we use it to create a new database for every customer that signs up on a project. We go in a loop of the list of tables, and use the CREATE TABLE statement to duplicate the table in a new name. This works across databases too (assuming you have permissions).

Of course, if you wanted to get all the data, you can do something like:
[sql]CREATE TABLE newtable LIKE oldtable;
INSERT INTO newtable SELECT * FROM oldtable;[/sql]

The full CREATE TABLE syntax is here.

Write a Comment

Comment

  1. A point to note is that you can crate a new table with indexes AND data in a single statement.

    CREATE TABLE new_collection (ID INT auto_increment primary key)
    SELECT artist, title, year FROM collection

    This will be useful when you write a standard create table statement and add dummy data from another table. For e.g.
    CREATE TABLE mynew
    ( PRIMARY KEY (nation), KEY fsearchIp (searchip)
    ) ENGINE = MEMORY DEFAULT CHARACTER SET utf8
    SELECT * FROM world

  2. Your post helped me much. I was using “CREAT TABLE newtable SELECT * FROM oldtable” which correctly creates a table with data and structure of first table but it does not copy the indexes. This problem solved by your post.

    Many Thanks.

  3. How to copy table structure with data from one schema to another schema in same database using SQL SERVER.

    my scenario is i have default data in DB, when ever i create new user, i want to create a schema with that username and copy existing tables with contraints and data in SQL SERVER.

Webmentions

  • MySQL: How to clone a table | Geekality February 17, 2011

    […] over a blog post on how you can copy a MySQL table with schema and keys, and boy was it easy. CREATE TABLE newtable LIKE […]

    • Related Content by Tag