Mysql Copy Data To Another Table

[Deno]

I'm curious, Could someone please tell me how do I copy the data I have in my mySQL tables to another mySQL table? I have a lot of data that I wanna transfer, and I'd hate to just type them all in again manually , and I'm also using the phpMyAdmin in the CPanel too.

 

Any help would be really really appreciated as always, because.... Rock Sign

 

Deno.

[Wilexa]

Are you just trying to duplicate a table?

 

If so, you can click on any table in phpmyadmin (on the left side of the page).

 

In the SQL command box near the bottom of the page enter:

 

>CREATE TABLE new_tbl SELECT * FROM orig_tbl;

 

where new_tbl is your new table name and orig_tbl is the table you are copying.

 

Then click on the "go" button.

 

HTH,

 

dave

[Deno]

No, I'am trying to copy existing data from one table to another. I don't get how that works. It seems like I'm making a new table, but I don't see how that transfers my original data to the new table.

 

Plus, I already have some data in my new table anywho.

[Wilexa]

Deno...

 

Sorry, I misunderstood your question.

 

 

Could you be a little more specific?

 

Are you trying to copy certain fields of certain records from one table into a new table? Or all fields?

 

Are the two table structures identical?

 

Are the field names the same between the two tables for the data you want copied?

 

...dave

[Deno]

Deno...

 

Sorry, I misunderstood your question.

 

 

Could you be a little more specific?

 

Are you trying to copy certain fields of certain records from one table into a new table? Or all fields?

 

Are the two table structures identical?

 

Are the field names the same between the two tables for the data you want copied?

 

...dave

No prob Dave!

 

My answers:

 

1.) Sure!

 

2.) No, just the data.

 

3.) Yes, they are identical.

 

4.) Yes.

 

Deno.

[Wilexa]

Deno...

 

Try this in the sql command window...

 

>INSERT IGNORE INTO new_tbl SELECT * FROM orig_tbl;

 

This command will take all the data from orig_tbl and add it to new_tbl.

 

The IGNORE is only necessary if there might be duplicate keys. Of course, those records won't get copied over.

 

You can read thet full syntax in the MySQL manual page.

 

...dave

[DarqFlare]

Nice job Wilexa.. lol.

Edited May 6, 2004 by TCH-Robert

[Deno]

Thank you very much!! It worked!! However, I noticed that not all of my records got copied over, due to the duplicate primary keys. Is there another way I can transfer them?

[Deno]

Wait! I just tried using the "REPLACE" syntax and it worked. But now my records are appearing all over the place in my table, when say record #1 is in between 6 - 9. Why is this?

 

And wait a minute!! now some of my records are gone! I mean, replaced with the new ones I copied from the old table....

[Wilexa]

Oh no! I hope things didn't get too messed up. Back up/export your database often to aviod catastrophe. I send myself an email every night with a database dump.

 

When you say REPLACE it will overwrite things.

 

Is that what you really meant to do?

 

I hope you made a backup (probably a little late now).

 

If a lot of data was overwritten that you can't reproduce, drop a ticket a the help desk and they should be able to recover your database from the last backup (usually within the last couple of days). But do it soon.

 

Back to MySQL:

If you use the REPLACE (or leave out IGNORE), mySQL thinks that you want to overwrite an existing record with data from the "from" table that uses the same key.

 

If you don't care about the key, but just want to add the old data and get a new key assigned to the record, then you can specify every field/column (except the keys) explicitly.

 

...dave