Import table mysqldump into existing live table in mysql

,
1

Hi All,

Please guide me that I have a single table mysqldump and i want to import dump data into live table so is it possible ?

Scenario:

  1. sale table data backup dump having old data i.e 2020-2021 year data
  2. Live sale table having data from 2022 to till date data
    I want to import sale table backup table data into live sale table.

Please provide your valuable expertise on it.

Thanks
Daljit

2

Hello Daljit,

You can import the data to existing table but you need to verify a few things in advance. Some of them are:

  1. Is the dump you’re having a fulldump (with create info) or having just inserts?
  2. Do you have possibility of duplicate data? Do you want to skip it if so?
  3. Is your existing table PK values appropriately in progression than the data being loaded?
  4. Do you have referential integrity foreign keys in the table?

If you’re not sure, import the data in a separate database table / instance first. Verify and then proceed further.

create database temp_db;
- if you don't have create info in the table
cat dump.sql | mysql -uUSER -p  temp_db
- verify and then move between databases
insert into DB.live_table select * from temp_db.tmp_tbl;

  • Check if you have create info (look for drop table if exists, create table… commands ):
    less dump.sql

  • If there’s only sales table specific data (INSERTs) you can load the data to mysql.
    cat dump.sql | mysql -uUSER -p DATABASE

  • If you want to extract INSERTs from dump (assuming this is a single table dump)
    cat dump.sql | grep -i 'insert into' > inserts.sql

  • Verify your inserts.sql file content

  • Load dump
    cat inserts.sql | mysql -uUSER -p DATABASE

Finally:

  • Test whatever you’re planning to do first.
  • Have backups.
  • If required take downtime.