Home
Arjen's Journal - Copying a table in MySQL
Open Query: MySQL, Open Source & other ponderings

Arjen Lentz
Date: 2008-04-29 19:20
Subject: Copying a table in MySQL
Security: Public

This question often comes up, and the general answer given appears to be "CREATE TABLE ... SELECT ..."
But actually, that does not do what you might expect, as this statement creates a table structure based on the resultset of the select, so the column types may differ from your original table, and the table will not have indexes either.
The syntax does allow you to add and override pretty much everything, but since we were talking about copying, let's look at another way:

CREATE TABLE bar LIKE foo;
INSERT INTO bar SELECT * FROM foo;

This produces an exact copy of the original table, both structure and data, indexes and everything.
No, you can't combine these two into a single statement. Sorry ;-)

Post A Comment | 8 Comments | Add to Memories | Tell a Friend | Link


User: [info]anothersysadmin.wordpress.com
Date: 2008-04-29 14:31 (UTC)
Subject: There's a quicker path

Take a look at this ;)

http://anothersysadmin.wordpress.com/2008/01/17/create-a-copy-of-a-table-with-mysql/

basically:
mysql> CREATE TABLE new_destination_table SELECT * FROM source_table;

Reply | Thread | Link


Arjen Lentz
User: [info]arjen_lentz
Date: 2008-04-29 14:36 (UTC)
Subject: No there's not

Sorry, but that blog is wrong. This was the whole point of my post; see the first paragraph where I explain what CREATE ... SELECT actually does.

Edited at 2008-04-29 14:38 (UTC)

Reply | Parent | Thread | Link


User: [info]anothersysadmin.wordpress.com
Date: 2008-04-30 09:22 (UTC)
Subject: Re: No there's not

Sorry, I didn't read carefully :/ I'll update that post... thanks for the hint!

Reply | Parent | Thread | Link


User: [info]brianpapantonio
Date: 2008-04-29 14:32 (UTC)
Subject: Not exactly identical

Foreign key constraints aren't copied. Obtaining the definition from SHOW CREATE TABLE would seem to be the only foolproof way.

Tested 5.0.45

Reply | Thread | Link


Arjen Lentz
User: [info]arjen_lentz
Date: 2008-04-29 14:37 (UTC)
Subject: Re: Not exactly identical

Did you file that as a bug? What's the bug#?

Edited at 2008-04-29 14:37 (UTC)

Reply | Parent | Thread | Link


User: (Anonymous)
Date: 2008-04-29 14:41 (UTC)
Subject: Actually, you CAN do it in a single statement

You can put the table definition inline in the CREATE ... SELECT.

CREATE TABLE FOO (
... any legal table definition here ...
)
AS SELECT ... any legal SELECT statement here;

Reply | Thread | Link


Arjen Lentz
User: [info]arjen_lentz
Date: 2008-04-29 14:49 (UTC)
Subject: You're faking it ;-)

So how/when/where do you get the legal table definition?
You'll need to run a SHOW CREATE TABLE ...
That's still 2 statements total?

Reply | Parent | Thread | Link


User: [info]fmpwizard.myopenid.com
Date: 2008-05-01 03:15 (UTC)
Subject: One possible use for MySQL proxy

using this lua script http://forge.mysql.com/tools/tool.php?id=136 you could use only one statement like:

CREATE FULL TABLE bar LIKE SELECT * FROM foo;

Reply | Parent | Thread | Link
browse
Previous Entry
Next Entry
my journal
my journal
my friends
my calendar
my userinfo
My Website
links
Open Query - MySQL Training & Expertise
Technocation
The Swag Report - The Place to Share Your Bag 'O Swag
RepRap
julian cash PHOTOGRAHY
May 2008
 123
45678910
11121314151617
18192021222324
25262728293031
High Performance MySQL (2nd ed.)


By Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Arjen Lentz
summary
Arjen Lentz