0

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!

  • What RDBMS are you using? – Vérace Mar 12 '16 at 20:16
  • You don't really get push-button data migration like that. Chances are that you will have to write a set of data migration scripts to move your data across. – ConcernedOfTunbridgeWells Mar 12 '16 at 20:40
0

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
| improve this answer | |
  • Thanks a lot Vérace. One more question. Does exist more tools like SQuirreL SQL? – Peter Konc Mar 13 '16 at 19:54
  • Normally, tool recommendations are off-topic here, but you could try here or here. The ones I recommended are free (SQuirreL SQL is also Open Source) and good. A Google of "list of database tools" will give you more. If you liked my answer, perhaps you could mark it as helpful? :-) – Vérace Mar 13 '16 at 20:01
  • Yes, definitively. Thank you very much! :) – Peter Konc Mar 13 '16 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.