List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:October 7 2009 4:38pm
Subject:Re: Dump / restore rows in table?
View as plain text  
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
Dump / restore rows in table?John Oliver7 Oct
  • Re: Dump / restore rows in table?Dan Nelson7 Oct
  • RE: Dump / restore rows in table?Jerry Schwartz7 Oct
    • RE: Dump / restore rows in table?Jerry Schwartz7 Oct