Does exist software for transforming data from one DB schema to another?

[Peter_26]

I need to transform between two different database schema the same data. For example i have one table in old schema looking like this:

OLD EMPLOYEE TABLE
USER_ID - NUMBER (PRIMARY KEY)
FIRST_NAME - VARCHAR(50), (NOT NULL)
LAST_NAME - VARCHAR(50), (NOT NULL)
CITY - VARCHAR(50), (NOT NULL)

and i need to transform it to new tables like these:

NEW EMPLOYEE TABLE
USER_ID - NUMBER (PRIMARY KEY)
FIRST_NAME - VARCHAR(50), (NOT NULL)
LAST_NAME - VARCHAR(50), (NOT NULL)
CITY_ID - NUMBER, (NOT NULL, FOREIGN KEY)

NEW CITY TABLE
CITY_ID - NUMBER (PRIMARY KEY)
CITY_NAME - VARCHAR(50), (NOT NULL)

So I have two questions to ask:

1. Does exist software that can migrate data between these two schema (Without or with little usage of SQL)?
2. If it exist, can this software migrate even between two platforms (I.e. MSSQL to Oracle)?

Thanks!

[hericles]

You can do it with a couple of SQL statements. First create your new tables if you haven't already then fill the cities table by doing:
INSERT INTO city(city_name) select distinct city from old_employee;

Then fill the new employee table by doing:

INSERT INTO new_employee(first_name, last_name, city_id)
SELECT e.first_name, e.last_name, c.city_id FROM old_employee e
JOIN city c ON c.city = e.city;

[rch1231]

You could do this quite easily with SQL by creating the new tables in your current database and then exporting the tables to be imported to the new database.

To dreate the NEW CITY you could use:

drop TABLE IF EXISTS `NEW CITY TABLE`;
CREATE TABLE `NEW CITY TABLE` 
(
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `City` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `City` (`City`)
) 
SELECT DISTINCT
`OLD EMPLOYEE TABLE`.City as City
FROM
`OLD EMPLOYEE TABLE`
GROUP BY
`OLD EMPLOYEE TABLE`.City

Then create the new employee table with :

drop TABLE IF EXISTS `NEW EMPLOYEE TABLE`;
CREATE TABLE `NEW EMPLOYEE TABLE` 
(
  `USER_ID` int(11) unsigned NOT NULL,
  `FIRST_NAME` VARCHAR(50) NOT NULL,
  `LAST_NAME` VARCHAR(50) NOT NULL,
  `CITY_ID` int(11) NOT NULL
  PRIMARY KEY (`USER_ID`)
) 
SELECT
`OLD EMPLOYEE TABLE`.`USER_ID`,
`OLD EMPLOYEE TABLE`.`FIRST_NAME`,
`OLD EMPLOYEE TABLE`.`LAST_NAME`,
`NEW CITY TABLE`.ID as `CITY_ID`
FROM
Clients
INNER JOIN `NEW CITY TABLE` ON `OLD EMPLOYEE TABLE`.`CITY` = `NEW CITY TABLE`.City

You would now have your two new tables and just have to export them to CSV or as sql and then import them to new database.
Hope that helps...