CODEWITHSUNDEEP
mysql
A Question Asker
A Question Asker

Reputation: 3311

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?

Upvotes: 13

Views: 21190

Answers (3)

singh1469
singh1469

Reputation: 2044

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.

Upvotes: 27

dlink
dlink

Reputation: 1595

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

Upvotes: 13

Mike Purcell
Mike Purcell

Reputation: 19979

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.

Upvotes: 1

Related Questions