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 <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/blink.gif" style="vertical-align:middle" emoid=":blink:" border="0" alt="blink.gif" />, and I'm also using the phpMyAdmin in the CPanel too. <br /><br />Any help would be really really appreciated as always, because.... Rock Sign <br /><br />Deno.<!--content-->
Are you just trying to duplicate a table?<br /><br />If so, you can click on any table in phpmyadmin (on the left side of the page).<br /><br />In the SQL command box near the bottom of the page enter:<br /><br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->CREATE TABLE new_tbl SELECT * FROM orig_tbl;<!--c2--></div><!--ec2--><br /><br />where new_tbl is your new table name and orig_tbl is the table you are copying.<br /><br />Then click on the "go" button.<br /><br />HTH,<br /><br />dave<!--content-->
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.<br /><br />Plus, I already have some data in my new table anywho.<!--content-->
Deno...<br /><br />Sorry, I misunderstood your question. <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/dry.gif" style="vertical-align:middle" emoid="<_<" border="0" alt="dry.gif" /> <br /><br /><br />Could you be a little more specific? <br /><br />Are you trying to copy certain fields of certain records from one table into a new table? Or all fields?<br /><br />Are the two table structures identical?<br /><br />Are the field names the same between the two tables for the data you want copied?<br /><br />...dave<!--content-->
<!--QuoteBegin-Wilexa+May 5 2004, 10:06 AM--><div class='quotetop'>QUOTE(Wilexa @ May 5 2004, 10:06 AM)</div><div class='quotemain'><!--QuoteEBegin-->Deno...<br /><br />Sorry, I misunderstood your question. <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/dry.gif" style="vertical-align:middle" emoid="<_<" border="0" alt="dry.gif" /> <br /><br /><br />Could you be a little more specific? <br /><br />Are you trying to copy certain fields of certain records from one table into a new table? Or all fields?<br /><br />Are the two table structures identical?<br /><br />Are the field names the same between the two tables for the data you want copied?<br /><br />...dave<!--QuoteEnd--></div><!--QuoteEEnd--><br /> No prob Dave! <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/smile.gif" style="vertical-align:middle" emoid="
" border="0" alt="smile.gif" /> <br /><br />My answers: <br /><br />1.) Sure! <br /><br />2.) No, just the data. <br /><br />3.) Yes, they are identical.<br /><br />4.) Yes.<br /><br /><img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/smile.gif" style="vertical-align:middle" emoid="" border="0" alt="smile.gif" /> Deno.<!--content-->
Deno...<br /><br />Try this in the sql command window...<br /><br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->INSERT IGNORE INTO new_tbl SELECT * FROM orig_tbl;<!--c2--></div><!--ec2--><br /><br />This command will take all the data from orig_tbl and add it to new_tbl.<br /><br />The IGNORE is only necessary if there might be duplicate keys. Of course, those records won't get copied over.<br /><br />You can read thet full syntax in the <a href="http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html" target="_blank">MySQL manual page</a>.<br /><br />...dave<!--content-->
Nice job Wilexa.. lol.<!--content-->
Thank you very much!! It worked!! <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/biggrin.gif" style="vertical-align:middle" emoid="
" border="0" alt="biggrin.gif" /> 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?<!--content-->
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?<br /><br />And wait a minute!! now some of my records are gone! I mean, replaced with the new ones I copied from the old table.... <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/ohmy.gif" style="vertical-align:middle" emoid="
" border="0" alt="ohmy.gif" /><!--content-->
Oh no! <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/sad.gif" style="vertical-align:middle" emoid="
" border="0" alt="sad.gif" /> 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.<br /><br />When you say REPLACE it will overwrite things.<br /><br />Is that what you really meant to do?<br /><br />I hope you made a backup (probably a little late now).<br /><br />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.<br /><br />Back to MySQL:<br />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.<br /><br />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.<br /><br />...dave<!--content-->
Are you just trying to duplicate a table?<br /><br />If so, you can click on any table in phpmyadmin (on the left side of the page).<br /><br />In the SQL command box near the bottom of the page enter:<br /><br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->CREATE TABLE new_tbl SELECT * FROM orig_tbl;<!--c2--></div><!--ec2--><br /><br />where new_tbl is your new table name and orig_tbl is the table you are copying.<br /><br />Then click on the "go" button.<br /><br />HTH,<br /><br />dave<!--content-->
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.<br /><br />Plus, I already have some data in my new table anywho.<!--content-->
Deno...<br /><br />Sorry, I misunderstood your question. <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/dry.gif" style="vertical-align:middle" emoid="<_<" border="0" alt="dry.gif" /> <br /><br /><br />Could you be a little more specific? <br /><br />Are you trying to copy certain fields of certain records from one table into a new table? Or all fields?<br /><br />Are the two table structures identical?<br /><br />Are the field names the same between the two tables for the data you want copied?<br /><br />...dave<!--content-->
<!--QuoteBegin-Wilexa+May 5 2004, 10:06 AM--><div class='quotetop'>QUOTE(Wilexa @ May 5 2004, 10:06 AM)</div><div class='quotemain'><!--QuoteEBegin-->Deno...<br /><br />Sorry, I misunderstood your question. <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/dry.gif" style="vertical-align:middle" emoid="<_<" border="0" alt="dry.gif" /> <br /><br /><br />Could you be a little more specific? <br /><br />Are you trying to copy certain fields of certain records from one table into a new table? Or all fields?<br /><br />Are the two table structures identical?<br /><br />Are the field names the same between the two tables for the data you want copied?<br /><br />...dave<!--QuoteEnd--></div><!--QuoteEEnd--><br /> No prob Dave! <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/smile.gif" style="vertical-align:middle" emoid="
" border="0" alt="smile.gif" /> <br /><br />My answers: <br /><br />1.) Sure! <br /><br />2.) No, just the data. <br /><br />3.) Yes, they are identical.<br /><br />4.) Yes.<br /><br /><img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/smile.gif" style="vertical-align:middle" emoid="" border="0" alt="smile.gif" /> Deno.<!--content-->Deno...<br /><br />Try this in the sql command window...<br /><br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->INSERT IGNORE INTO new_tbl SELECT * FROM orig_tbl;<!--c2--></div><!--ec2--><br /><br />This command will take all the data from orig_tbl and add it to new_tbl.<br /><br />The IGNORE is only necessary if there might be duplicate keys. Of course, those records won't get copied over.<br /><br />You can read thet full syntax in the <a href="http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html" target="_blank">MySQL manual page</a>.<br /><br />...dave<!--content-->
Nice job Wilexa.. lol.<!--content-->
Thank you very much!! It worked!! <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/biggrin.gif" style="vertical-align:middle" emoid="
" border="0" alt="biggrin.gif" /> 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?<!--content-->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?<br /><br />And wait a minute!! now some of my records are gone! I mean, replaced with the new ones I copied from the old table.... <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/ohmy.gif" style="vertical-align:middle" emoid="
" border="0" alt="ohmy.gif" /><!--content-->Oh no! <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/sad.gif" style="vertical-align:middle" emoid="
" border="0" alt="sad.gif" /> 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.<br /><br />When you say REPLACE it will overwrite things.<br /><br />Is that what you really meant to do?<br /><br />I hope you made a backup (probably a little late now).<br /><br />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.<br /><br />Back to MySQL:<br />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.<br /><br />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.<br /><br />...dave<!--content-->