Replace data in column with foreign key to same data in other table

Question

Hopefully you guys can help me figure out how to do something I haven't come across before.

I have a table, call it TableA, with many columns. One of them is 'state', with entries such as:

state
'MA'
'NJ'
'HI'

and so forth. I would like to create some sort of way to pull these values out into a new foreign-keyed table, call it StateTable, which would have columns state_key and state.

For example, here's how I envision it proceeding row-by-row:

Row 1: Value in 'state' column in TableA is 'MA'. Check to see if StateTable has an entry where the 'state' column is 'MA'. If so, get the state_key for that row, and replace the entry in TableA with the foreign key, so that row now has a FK to 'MA' instead of storing that value directly. If StateTable has no 'MA' entry, insert it, and do the same with the new FK. And so on, for each row.

So the end result would be two tables:

TableA

state
1
2
3

StateTable

state_key    state
1            'MA'
2            'NJ'
3            'HI'

There shouldn't be any hard-coded stuffs going on because I'll need to do this for other columns as well, like state, that have a somewhat small number of finite values.

tl;dr a way to preserve data in a column while turning the column into a FK'd table.

Any ideas? Thanks!!

Answer 1

Create the state table with an auto-enumeration field "state_key", use SELECT DISTINCT ... INTO to fill it, and use something like

UPDATE TableA SET TableA.state=
   (SELECT state_key FROM StateTable where TableA.state=StateTable.state)

to get the values into it.

Answer 2

My suggestion would be to do the following

1. Create your new state table (either manually or using an insert into statement)
2. if manually creating your state table, insert unique records into it from TableA
3. Create a new column in TableA (StateID) foreign key column, linked to your new state table
4. Insert value into your new StateID column by matching TableA state to the state in your state table and pulling the ID from that table.

If you need a hand with the queries, let me know.