Transfering records with foreign key referring to new value

Question

In an SQLite DB, I have to transfer some records from two tables (old_warnings and old_acknowledges) to other two tables(new_warnings and new_acknowledges).

Transfering warnings is not a problem.

INSERT INTO new_warnings (event, comment, value)
SELECT event, comment, value
FROM old_warnings
WHERE old_warnings.event = 'one';

However, the acknowledges tables have the reference to the warning (warning_id:integer) it refers to and if I transfer the acknowledges first, I end up having the old warning_id in the table. But, if I transfer the warnings first, I don't have any reference to the new id to insert in the new_acknowledges table.

Any idea of how to handle this situation?

Answer 1

There could be few approaches you can take to achieve this... based onhow big is the data set.

1. Use of cursors Using cursor you can insert each row is new warning table capture the new_warningid created for that row and use it to insert respective Acknowledges referring to old_warning id and replacing with new_warningid.

2. use of temporary column in New_warning table which will hold old_warningid for lookup. Add a column "old_warningid" to new_warnings table and use that column as lookup while inserting rows in new_acknowldges tables to get corresponding "new_warningid".once the migration is complete you can drop the columns from new_warnings table.