1

I have database named "Testing". Now I have to do restore data from new dump file which is having Old + New data to "Testing" database. I want to restore only New data to database (without duplicating old data ) .

Tried taking backup of a portion of table and then restoring it ( How to use mysqldump for a portion of a table?), But it did't work out because No column like ID is in my table (primary Key for row count )

My table structure is like this ,

 A              | B          | C       | D     | E       |
+---------------+------------+---------+-------+---------+
| 1432550740000 |     111004 | 10000   |     2 | 9690001 |
| 1432550790000 |     123001 | 10000   |     2 | 9690001 |
| 1432550340000 |   44440215 | 10000   |     2 | 9690001 |
| 1432550450000 |  125400054 | 10000   |     2 | 9690001 |
| 1432551444000 |   43459067 | 10000   |     2 | 9690001 |
+---------------+------------+---------+-------+---------+

Can anyone please guide me with this ?

Community
  • 1
  • 1
Vidya
  • 23
  • 3
  • How do you decide which data you want to backup? You don't need a id-field to write the query, any valid sql delivering your expected resultset should do the trick (if you can use `SELECT .. INTO OUTFILE ..`). – Philipp Jun 28 '16 at 10:42
  • @Philipp I will decide based on row count of Old table . For example If old table consists X rows means , New dump will have X+Y rows . In that I want to restore Y rows . – Vidya Jun 28 '16 at 10:48
  • Ah so it is already too late to create a dump with only the new data? – Philipp Jun 28 '16 at 10:53

1 Answers1

0

You can take a little detour by creating a new table (same structure as old one) and dump your data of old and new data into this one.

You then can delete the rows from the new data that already exist in your other table. (Ideally you have something in your data to identify your new Values, but it will also work with limit if you write the query yourself)

DELETE FROM newTable WHERE `identifier` IN (SELECT `identifier` FROM oldTable);
DELETE FROM newTable LIMIT 12; -- deletes first 12 entries

Now you only have new data in the new table. You can then truncate the old table and insert the new data into the old table.

TRUNCATE oldTable;
INSERT INTO oldTable SELECT * FROM newTable;

And then you can truncate, empty or delete the newTable, you don't need it anymore.

Philipp
  • 2,787
  • 2
  • 25
  • 27
  • 1.deleting the data based on identifier: This will give Only new data table ..its useful if I have atleast one Unique column :/ (No unique column in mine ) ..... 2.deleting using limit: deleting old table count records from new table sounds good .but I cant use this as solution, why because If old table data also changed means I may delete new data using this (chances are there ) .... Finally I got solution, If I do restore data using new dump file in same database "Testing" the X+Y records coming in old table itself (not duplicating) .... Thank you so much for your guidance . – Vidya Jun 28 '16 at 12:35