Hello v_pozidis
Here is a simple example but that I used on a real database of mine. It's just 3 tables of the complete scheme but the approach is the same for all.
OLD TABLES STRUCTURE
As you can see, the assets and the artist are linked through a join table on their respective database IDs, that is standard and probably what most of us do.
Now, over time:
“holes” have been produced in all three tables because of delete and add operations
New needs arose and some fields need to be added to both tables or anything else that could justify having to transfer data to new tables
The question is: how do I transfer all data in new tables, but without loosing the correct links since (unless copying the old IDs over) new unique key IDs will be produced on insert?
Here is the new table’s structure:
Note that:
The join table is not yet linked
Both the artists and assets tables have a field cold “old_id”
The join table also included the “old_id”, that’s the reference we will be copying
The first step is to copy all assets and artists over to the new tables, leaving the new IDs up to the database engine but storing the old ones in the corresponding fields
Transferring the old assets to the new asset table is done like (it is also a good time to sort the select query on any field you want to “clean” the table):
INSERT INTO data_assets(asset_name,asset_old_id)
SELECT
asset_name,
id
FROM asset
ORDER BY
asset.id_vendor ASC,
CAST(asset_SKU AS INTEGER) ASC
Same things applies to the artist table:
INSERT INTO dico_artists(artist_name,artist_old_id)
SELECT
artist_name,
id
FROM
Artist
ORDER BY UPPER(artist_name)
Finally, the join table is transferred, but we transfer only the old IDs
INSERT INTO link_artist_asset(asset_old_id,artist_old_id)
SELECT
id_asset,
id_artist
FROM
artist_asset
DON’T MIX THE ORDER OF THE IDs OR YOU’LL GET IN TROUBLE
It’s time now to update the “new ID” fields based on the new IDs generated while transferring the artists and the assets to their new tables. I do it in two passes:
UPDATE link_artist_asset SET artist_id =
(
SELECT artist_id FROM dico_artists WHERE link_artist_asset.artist_old_id = dico_artists.artist_old_id
)
My second query is almost identical but it is updating 185.000 records searching for IDs in a table containing 235.000 records (when there were only 4.500 in the artist table). It was still running after 3.600 seconds. So I stopped it and built and index on the data_assets table based on asset_id and asset_old_id.
UPDATE link_artist_asset SET asset_id =
(
SELECT asset_id FROM data_assets WHERE data_assets.asset_old_id = link_artist_asset.asset_old_id
)
> Affected rows: 184488
> Time: 2114,027s
It still ran for 35 minutes. Once done, just rebuild the foreign keys. This will be the test because of you get no error, it means that all IDs have been transferred and converted correctly.
You can also delete the asset_old_id and artist_old_id fields form the three tables if you want to gain some space or keep them for references if you need them later.
ALTER TABLE "main"."link_artist_asset" RENAME TO "_link_artist_asset_old_20220917";
CREATE TABLE "main"."link_artist_asset" (
"link_artist_asset_id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"asset_id" INTEGER,
"artist_id" INTEGER,
"asset_old_id" INTEGER,
"artist_old_id" INTEGER,
CONSTRAINT "fk_asset" FOREIGN KEY ("asset_id") REFERENCES "data_assets" ("asset_id"),
CONSTRAINT "fk_artist" FOREIGN KEY ("artist_id") REFERENCES "dico_artists" ("artist_id")
);
INSERT INTO "main"."sqlite_sequence" (name, seq) VALUES ("link_artist_asset", '184488');
INSERT INTO "main"."link_artist_asset" ("link_artist_asset_id", "asset_id", "artist_id", "asset_old_id", "artist_old_id") SELECT "link_artist_asset_id", "asset_id", "artist_id", "asset_old_id", "artist_old_id" FROM "main"."_link_artist_asset_old_20220917";
(This is correct for my tables, you Navicat will adapt to yours)
Here is the result with no errors
There is not much more to it: you need to be precise and… patient.
As I said, another approach would be to transfer data form old to new table with PRAGMA KEY off and force the transfer of the current IDs to the new tables. I do not like this method and do not trust myself enough to use it knowing I will probably forget something at one point or another
I hope this will be of some help for you. As I said, I had a look at your database and it's huge and... I don't read Greek
Cheers
Math
I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor