Avoid CASE clause while importing data to SQL Server

I have to import data from an old schema to a new one, where a column 'career_name' (in table 'users') that used to be a VARCHAR now should be an INTEGER which is a foreign key to another table careers. This way I intend to tipify the data that was stored in the VARCHAR column in order to keep integrity in my database.

Actually, I do the following:

1. Create both new tables in the new schema
2. Use SELECT DISTINCT 'career_name' FROM old_table in order to obtain all possible values
3. INSERT into my new table careers the rows obtained above
4. INSERT data into my new table users using a CASE clause in order to obtain the ID from table careers of the corresponding former career_name so the relation is created.

Now the proble开发者_运维技巧m is the following, the table careers is big, so writing one CASE clause for each row in the table in order to import users is nearly impossible.

Is there a way to avoid this? I wish I could use my table careers as an associative array, where the key was the former career_name and the value the row's ID... Thanks!

INSERT into new_Users 
SELECT Users.Name, careers.id
FROM Users inner join careers ON Users.career_name = careers.career_name

Because the schema isnt known to me, I am assuming that new_users table has 2 columns which is name and career_id.

Is there a way to avoid this? I wish I could use my table 'careers' as an associative array, where the key was the former 'career_name' and the value the row's ID... Thanks!

Er... That's what a table is, isn't it? Just join to your new careers table to use it as a lookup when doing the insert:

INSERT INTO users (blah, whatever, career_id)
SELECT
  old_users_table.blah,
  old_users_table.whatever,
  careers.career_id
FROM
  old_users_table INNER JOIN careers ON old_users_table.career_name = careers.career_name

... where users is your new users table, old_users_table is wherever you're getting the data you want to migrate, and careers is your new careers table.