I need to transform some data between two different database schemas. For example, I have one table in old schema like this:
and I need to transform it to new schema like this:
So I have few questions to ask:
Thanks!
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 DBAs :-).
I've used the script below for PostgreSQL to solve your problem. For simplicity, I haven't put in PRIMARY KEYs, 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
(
id serial,
lname VARCHAR(25),
city VARCHAR(25)
);
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
(
id serial,
city_name VARCHAR(25)
);
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
FROM employee;
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.
UPDATE employee
SET city_id = city.id
FROM city
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
