Mysqldump: Can you change the name of the table you're inserting into?

I am using mysqldump to transfer a table from one database to another.

mysqldump -h host -u user -p password -e --single-transaction --no-create- info --default-character-set=utf8 --complete-insert --result-file=thisisaresult db table

I was wondering, however, if there is a way to change the name of the table you insert into? For example, I'd like this to insert into table_staging, or something like that. Is this possible, or am I going to have to just use sed?

Answers


I don't think is possible when dumping data because there may be FK references to the table you are changing.

If there are no FK references to the table you wish to change then it is possible to just hand edit the resulting dump file:

CREATE TABLE `old_table_name`

Becomes

CREATE TABLE `new_table_name`

My recommendation would be to dump the data, re-import it into your new database, then run the alters to rename your table.


After creating a mysql dump file you could do the following:

sed -i 's/`old-table-name`/`new-table-name`/g' old-table-name.dump

The sed command will search and replace the old table name with the new table name from within the mysql dump file.


You can change table name on the fly when you create the dump using Unix sed.

# create dump of mytable, renaming it to mytable_new

$ mysqldump -umyuser -pmypass -hmyhost mydb mytable |\
  sed -e 's/`mytable`/`mytable_new`/'| gzip -c > mydb_mytable_new.dump.gz


# restoring in another database:

$ gunzip -c mydb_mytable_new.dump.gz | mysql -umyuser2 -pmypass2 -hmyhost2 mydb2

Need Your Help

Explananation of operator |=

c# operators

what does the operator |= actually do? When I checked in MSDN it is written as Assignment and Lambda expression. Do anyone clarify this with an example?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.