How to transform SQL table of data into table of pointers ?

Question

Let's say I have an SQL table of tuples (id, lastname, firstname, occupation) where all values are strings (ok, obviously id is a key).

I want to transform it to a table with tuples (id, lastid, firstid, occupid), where I only keep pointers to other tables that contain the actual values. I apologize if the example domain of names and occupations is not the best suited for this operation.

Obviously to create the other tables that will hold the data, I need to get all last names, unique, and insert them into a new table with an auto-generated key. The same with first names and occupations.

Having done that, is there a single transformation that can generate the new table containing the pointers (er, foreign keys) ?

Implementation uses SQLite, if it matters.

Answer 1

Assuming your tables for last/first names and occupations are Lnt, Fnt and Occ, each with just two columns, an id field and a value:

REPLACE INTO TheTable (last, first, occup)
SELECT Lnt.id, Fnt.id, Occ.id
FROM TheTable
JOIN Lnt ON (last=Lnt.value)
JOIN Fnt ON (first=Fnt.value)
JOIN Occ ON (occup=Occ.value)