I have had to face this problem recently and fixing it manually is tedious and avoiding errors by omission is not easy:
Scenario:
Preconditions:
I have a WORK table with a numeric station that auto-increments. It is the master table that has several associated tables:
- HISTORY: Saves all actions performed on a job.
- MESSAGES: Save all the communications of a job.
- ATTACHMENTS: Saves all attachments to a job.
- TASKS: A job can have specific subtasks that are stored here.
- ... Some 5 more tables related to WORK for an FK.
Problem : A user mistakenly deleted a job and the application when it deletes, does it really and cascaded ( disclaimer : it is not my implementation, we are working on modifying this part to make logical deletions).
My solution The solution was to pull backup: I mounted a temporary database on another machine and looked for the deleted job to re-insert it. But I had to search, table by table, the rest of the related records, copy them to a text file in CSV format and create the insert instructions for each record and insert them in order.
My question is: Is there any way, given an ID of the main table, to extract all the related data to insert them into another database with the same schema in an automated mode? For me the ideal would be to create a file with all insert ready to be executed.
In my case I work with PostgreSQL, but ideas are accepted for any database manager or command line scripts.