Jump to content

Mysql Copy Data To Another Table


Deno
 Share

Recommended Posts

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 :goof:, and I'm also using the phpMyAdmin in the CPanel too.

 

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

 

Deno.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Deno...

 

Sorry, I misunderstood your question. :P

 

 

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

Link to comment
Share on other sites

Deno...

 

Sorry, I misunderstood your question. :P

 

 

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.... :)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...