A) 0. optionally: start transaction; 1. create temporary table mytemptable select * from mytable order by mytableprimarykey limit 100000; 2. insert into destinationtable select * from mytemptable; 3. delete * from mytable order by mytableprimarykey limit 100000; 4. drop mytemptable; 5. optionally: commit; 6. Goto step 0.
if you don't want to "play it save", then you can skip step 1 and insert your data straight into the destination table. or B) Study the MySQL Manual for the mysqldump's commandline parameters, to dump all your data to a file first. Then recreate your table on the database/table you wish. or C) You can use many of the GUI tools around most of which can backup your data to scripts or other databases/tables. Ex. http://www.scibit.com/products/mycon 0. Double click your table, sort and limit your data as you wish 1. Click Backup 2. Use the resulting script against your destination table. 3. Hit Ctrl+A in your table's grid and then Del to delete the select records and then hit Refresh to start again (if you have selected only a 100,000 batch for example). 4. Goto step 1. By default your resulting script will contain records batched 100 per insert statement and thus for a 100,000 records you will only have a 1000 insert statements. This will of course execute much faster than 100,000 separate insert statements. It would be highly recommended to use an intermediate local "dump" script file (if your space problem is not a concern for your local computer), that way you have all your data backed up for "just in case". Kind Regards SciBit MySQL Team http://www.scibit.com > > -----Original Message----- > From: "J S" <[EMAIL PROTECTED]> > To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> > CC: > Subject: moving data > Sent: Thu, 02 Sep 2004 12:38:30 GMT > Received: Thu, 02 Sep 2004 12:40:48 GMT > Read: Thu, 02 Sep 2004 13:23:07 GMT > Hi, > > I want to copy data from one table to another. However I need to do this > carefully because I haven't got much of space left. I was thinking of maybe > selecting data from every 100000 rows of the old table, inserting it into > the new table, then deleting those rows from the old table. > > Could someone help me out with the SQL for this please? Or tell me if > there's a better way of doing this? > > Thanks, > > JS. > > _________________________________________________________________ > Want to block unwanted pop-ups? Download the free MSN Toolbar now! > http://toolbar.msn.co.uk/ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
