Drupal, PHP, Open Source and anything else I feel like sharing
Ceiling of the Pantheon

MySQL dump a table without the primary key (ID)

Today I had the need to dump a certain amount of rows from an existing database and insert them into a new table with the same structure. This application required me to not include the table structure nor the primary key (id). To get the database dump, I used the following command.

  1. mysqldump -u USER -p DATABASE_NAME TABLE_NAME --no-create-info --where='id>=29960000' | sed -e "s/([0-9]*,/(NULL,/gi" > FILENAME.sql

The first part is like any normal mysqldump, except I used the --no-create-info and --where flags to prevent the structure and to limit the results.

  1. mysqldump -u USER -p DATABASE_NAME TABLE_NAME --no-create-info --where='id>=29960000'

Then, I piped the results to the sed command to strip out the id field from the insert statements.

  1. sed -e "s/([0-9]*,/(NULL,/gi"

Thanks for the reference (http://stackoverflow.com/a/28704495).

This gave me exactly what I needed, all of the data without the table structure and the primary ids.

Comments