You'll need to use a multi-database tool - I would recommend SQuirreL SQL - it's a Java based tool that connects through JDBC. If you want to migrate to Oracle, you could also consider SQL Developer (which IIRC can connect to MS SQL through JDBC also).
As @ConcernedOfTunbridgeWells said in his comment, you can't get a "push-button" migration of data - you have to explicitly tell the different systems from what column they're to take data and where to put it and join the tables back and forth. If it could be done automatically, there'd be no need for programmers or
I've used the script below for PostgreSQL to solve your problem. For simplicity, I haven't put in
FOREIGN KEYs or
INDEXes which may (probably) help with performance.
1st I created the employee table and put a small amount of sample data into it.
CREATE TABLE employee
INSERT INTO employee (lname, city) VALUES('Lino', 'Dublin');
INSERT INTO employee (lname, city) VALUES('Mary', 'Cork');
INSERT INTO employee (lname, city) VALUES('Bill', 'Galway');
INSERT INTO employee (lname, city) VALUES('Fred', 'Dublin');
Below is not strictly necessary - but it's always a good idea to "eyeball" your data, particularly when testing!
SELECT * FROM employee; <--- result snipped for brevity.
Create the new table city.
CREATE TABLE city
Now, insert into this new city table, the
DISTINCT values from the city field in your employee table.
INSERT INTO city (city_name)
SELECT DISTINCT city
And again, to double-check.
SELECT * FROM city; <---again, snipped.
Add a column city_id to the employee table.
ALTER TABLE employee ADD COLUMN city_id INTEGER; <-- syntax may vary according to RDBMS.
You can select again from the table to double-check (step skipped for brevity).
Next, you have to update the employee table with the corresponding id value that you've created in the new city table. The syntax for this step may vary according to your RDBMS - I found this post helpful.
SET city_id = city.id
WHERE city.city_name = employee.city;
SELECT * FROM employee; <--- snipped for brevity.
Drop the city column from the employee table. Again, step may vary.
ALTER TABLE employee DROP COLUMN city;
SELECT * FROM employee;
Result - et voila!
id; lname city_id
3; Bill 1
2; Mary 2
4; Fred 3
1; Lino 3