Shoutbox

Hi,

I need this help quick.
I'm still looking for the answer, but I need help from some people who are knowledgeable in SQL.

How to copy tables from a database to another database using a single query?
I'm using mySQL if that matters.

Thanks.

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

INSERT INTO targetDB.targetTable SELECT * FROM sourceTable

Of course, you will need to create the target table and set up its structure first. I don't know of a way to copy the structure and data with one query.

Edit: For quickly copying the structure, maybe you could use...

CREATE TABLE targetDB.targetTable LIKE sourceTable

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

Edit 2: Upon further reading of the above link, it seems that you can do...

CREATE TABLE targetDB.targetTable SELECT * FROM sourceTable

To copy the structure and data at once. Amazing.

It works

I didn't know that I could use INSERT INTO.
So, I'm guessing that I can append the target table using the same query.
I can or I can't?

Answer: yes.

quote:

---

Originally posted by thekid
So, I'm guessing that I can append the target table using the same query.

---

Yeah, sure, but keep in mind that you might have problems with duplicate rows/keys. Read the info in the manual about ON DUPLICATE KEY UPDATE and the IGNORE keyword.

Hi,

You can't quite copy a database consisting of multiple tables with a single query, but you can do it with a single shell command.  Look here, under the heading "Using mysqldump to copy databases", toward the bottom of the page.