SQL Command for copying table

Question

What is the SQL command to copy a table from one database to another database? I am using MySQL and I have two databases x and y. Suppose I have a table in x called a and I need to copy that table to y database. Sorry if the question is too novice.

Thanks.

Answer 1

If you just want to copy the contents, you might be looking for select into: http://www.w3schools.com/Sql/sql_select_into.asp. This will not create an identical copy though, it will just copy every row from one table to another.

Answer 2

If the target table doesn't exist....

CREATE TABLE dest_table AS (SELECT * FROM source_table);

If the target table does exist

INSERT INTO dest_table (SELECT * FROM source_table);

Caveat: Only tested in Oracle

Answer 3

Since your scenario involves two different databases, the correct query should be...

INSERT INTO Y..dest_table (SELECT * FROM source_table);

Query assumes, you are running it using X database.

Answer 4

If your two database are separated, the simplest thing to do would be to create a dump of your table and to load it into the second database. Refer to your database manual to see how a dump can be performed.

Otherwise you can use the following syntax (for MySQL)

INSERT INTO database_b.table (SELECT * FROM database_a.table)

Answer 5

At the command line

mysqldump somedb sometable -u user -p | mysql otherdb -u user -p

then type both passwords.

This works even if they are on different hosts (just add the -h parameter as usual), which you can't do with insert select.

Be careful not to accidentally pipe into the wrong db or you will end up dropping the sometable table in that db! (The dump will start with 'drop table sometable').

Answer 6

insert blah from select suggested by others is good for copying the data under mysql.

If you want to copy the table structure you might want to use the show create table Tablename; statement.

Answer 7

Please check this link.. link text