duplicate a table

Anon

can you please tell me how to duplicate a table? I tried this
create table refer2 select * from refer;

it didn't work.

thanks in advance

kparker

What dbms are you using? For many of them, you can't elide the column definitions.

Anon

i am a newbie, i amn ot sure what a dbms is. 🙁

shanx24

You could try

create table refer2 as select * from refer;

Notice the "as" keyword that you might have missed.
DBMS = DataBase Management System. I guess the question was which database you are using - MySQL, Oracle, SQL Server etc?

Hope I helped.

Anon

I am using mysql.
create table refer2 as select from refer;
didn't work. It said "ERROR 1064: You have an error in your SQL syntax near 'as select from refer' at line 1"

shanx24

Which version of mysql are you using?

Try this:

mysql> select version();
+---------------+
| version() |
+---------------+
| 3.23.28-gamma |
+---------------+
1 row in set (0.11 sec)

Here is my MySQL screen dump:

mysql> create table test3 as select * from test;
Query OK, 3 rows affected (0.26 sec)
Records: 3 Duplicates: 0 Warnings: 0

Anon

mysql> select version();
+-----------+
| version() |
+-----------+
| 3.22.32 |
+-----------+
1 row in set (0.00 sec)

shanx24

Hmm..beats me then! Both with and without the "as" works for me...I am on W2K at home.

steved

A lot of databases support the following format to duplicate a table structure and all of the data.

Select * into refer2 from refer

If you just want the structure without the data, give it a where clause that will never be true

select * into refer2 from refer where 1 = 2

Anon

Hi!

this is one way to do it

mysqldump -u<user> -p<password> <database>

test.tst

and then something like, don't exactly remember, but I can check it out for you if you wanna..

mysql -Bsu <user> -e test.tst <database> > outfile.txt

you can reach me at johan.brunius@collect.se

smarlowe

But be careful. Many databases won't create the destination table if there are no result rows form the select into statement.

I do this:

select * into new_table from old_table limit 1;
delete from new_table;

Most optimizers will pump through these two quickly, and it's fairly portable.

Anon

select * into new_table from old_table limit 1;

didn't work. Syntax error, it said.

mysql> select version();
+-----------+
| version() |
+-----------+
| 3.22.32 |
+-----------+
1 row in set (0.00 sec)