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?
Best Answer
There could be few approaches you can take to achieve this... based onhow big is the data set.
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.
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.