Dump / restore rows in table?

I did try to find out how to do this in the manual, but "row" and
"table" occur so many times...

I want to dump a certain number of rows from one table, and then restore
them to another database. I'm guessing I'd try "mysqldump -u root
-pPASSWORD database_name table_name" and then add something to specify
rows 1000-1050. And then I'm guessing that mysql < result.sql would
restore? Or would it not know what table it came from, and I'd have to
specify that?

--
************************************************************ ***********
* John Oliver http://www.john-oliver.net/ *
* *
************************************************************ ***********

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Dump / restore rows in table?

In the last episode (Oct 07), John Oliver said:
> I did try to find out how to do this in the manual, but "row" and
> "table" occur so many times...
>
> I want to dump a certain number of rows from one table, and then restore
> them to another database. I'm guessing I'd try "mysqldump -u root
> -pPASSWORD database_name table_name" and then add something to specify
> rows 1000-1050. And then I'm guessing that mysql < result.sql would
> restore? Or would it not know what table it came from, and I'd have to
> specify that?

If the two tables are on the same mysql server, just use "insert into
newtable select * from oldtable where rowid between 1000 and 1050". If
they're on two different servers, this syntax will output only insert
statements (and no table creation or optimization options):

mysqldump --no-create-info --compact --where 'rowid between 100 and 1050' mydb oldtable

Note that the inserts include the table name, so if you're loading into a
different table, you'll need to rewrite the output with sed, or maybe create
a "select * from oldtable" view on your first server that matches the table
name on the second server, so your table names match.

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Dump / restore rows in table?

Are you just trying to copy a subset of one table into another? If so, simply
do this:

CREATE TABLE new_one SELECT * FROM old_one LIMIT 1000,5000;

That will create a table with the same columns, but no keys or such. If you
want to copy the key structure, it will take you two commands:

CREATE TABLE new_one LIKE old_one;
INSERT INTO new_one SELECT * FROM old_one LIMIT 1000,5000;

If you want a random selection (say 1% of your original records), use

.... WHERE RAND() < .01;

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com


>-----Original Message-----
>From: John Oliver [mailto:joliver@john-oliver.net]
>Sent: Wednesday, October 07, 2009 12:29 PM
>To: mysql@lists.mysql.com
>Subject: Dump / restore rows in table?
>
>I did try to find out how to do this in the manual, but "row" and
>"table" occur so many times...
>
>I want to dump a certain number of rows from one table, and then restore
>them to another database. I'm guessing I'd try "mysqldump -u root
>-pPASSWORD database_name table_name" and then add something to specify
>rows 1000-1050. And then I'm guessing that mysql < result.sql would
>restore? Or would it not know what table it came from, and I'd have to
>specify that?
>
>--
>*********************************************************** ************
>* John Oliver http://www.john-oliver.net/ *
>* *
>*********************************************************** ************
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>infoshop.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Dump / restore rows in table?

>-----Original Message-----
>From: Jerry Schwartz [mailto:jschwartz@the-infoshop.com]
>Sent: Wednesday, October 07, 2009 2:15 PM
>To: 'John Oliver'; mysql@lists.mysql.com
>Subject: RE: Dump / restore rows in table?
>
[JS] I should have mentioned that you can do this even if you are creating a
table in another database (on the same host).

>Are you just trying to copy a subset of one table into another? If so, simply
>do this:
>
>CREATE TABLE new_one SELECT * FROM old_one LIMIT 1000,5000;
>
>That will create a table with the same columns, but no keys or such. If you
>want to copy the key structure, it will take you two commands:
>
>CREATE TABLE new_one LIKE old_one;
>INSERT INTO new_one SELECT * FROM old_one LIMIT 1000,5000;
>
>If you want a random selection (say 1% of your original records), use
>
>... WHERE RAND() < .01;
>
>Regards,
>
>Jerry Schwartz
>The Infoshop by Global Information Incorporated
>195 Farmington Ave.
>Farmington, CT 06032
>
>860.674.8796 / FAX: 860.674.8341
>
>www.the-infoshop.com
>
>
>>-----Original Message-----
>>From: John Oliver [mailto:joliver@john-oliver.net]
>>Sent: Wednesday, October 07, 2009 12:29 PM
>>To: mysql@lists.mysql.com
>>Subject: Dump / restore rows in table?
>>
>>I did try to find out how to do this in the manual, but "row" and
>>"table" occur so many times...
>>
>>I want to dump a certain number of rows from one table, and then restore
>>them to another database. I'm guessing I'd try "mysqldump -u root
>>-pPASSWORD database_name table_name" and then add something to specify
>>rows 1000-1050. And then I'm guessing that mysql < result.sql would
>>restore? Or would it not know what table it came from, and I'd have to
>>specify that?
>>
>>--
>>********************************************************** *************
>>* John Oliver http://www.john-oliver.net/ *
>>* *
>>********************************************************** *************
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>>infoshop.com
>
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>infoshop.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org