Software for Transforming Data Between DB Schemas – Options and Recommendations

database-designmigrationreplicationschema

I need to transform some data between two different database schemas. For example, I have one table in old schema like this:

Employee table - Old schema

and I need to transform it to new schema like this:

Tables describing Employee - New schema

So I have few questions to ask:

  1. Does there exist software that can migrate data between these two schemas (Without or with little usage of SQL)?
  2. If it exists, can this software migrate even between two platforms (i.e. MS SQL Server to Oracle)?

Thanks!

Best Answer

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
Related Question