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.
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
Hi! Another nice way to get the table structure schema is to do: SHOW CREATE TABLE mytable;
Nice post, cheers!
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.
Thanks!!!
That solve a problem I had.
🙂
Cheers!
Thanks! effective tricks.
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.
I have used a stored procedure in similar situations. You can use create table … select from syntax with SQL Server too.