Deno Posted May 5, 2004 Share Posted May 5, 2004 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. Quote Link to comment Share on other sites More sharing options...
Wilexa Posted May 5, 2004 Share Posted May 5, 2004 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 Quote Link to comment Share on other sites More sharing options...
Deno Posted May 5, 2004 Author Share Posted May 5, 2004 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. Quote Link to comment Share on other sites More sharing options...
Wilexa Posted May 5, 2004 Share Posted May 5, 2004 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 Quote Link to comment Share on other sites More sharing options...
Deno Posted May 5, 2004 Author Share Posted May 5, 2004 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. Quote Link to comment Share on other sites More sharing options...
Wilexa Posted May 5, 2004 Share Posted May 5, 2004 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 Quote Link to comment Share on other sites More sharing options...
DarqFlare Posted May 6, 2004 Share Posted May 6, 2004 (edited) Nice job Wilexa.. lol. Edited May 6, 2004 by TCH-Robert Quote Link to comment Share on other sites More sharing options...
Deno Posted May 6, 2004 Author Share Posted May 6, 2004 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? Quote Link to comment Share on other sites More sharing options...
Deno Posted May 6, 2004 Author Share Posted May 6, 2004 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.... Quote Link to comment Share on other sites More sharing options...
Wilexa Posted May 6, 2004 Share Posted May 6, 2004 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.