transfer or migration data between diferent structure databases

[never]

Hello,
I would like to migration data from one database with some data structure to the other database with completly diferent structure...both are created already

for example
1. old table with this structure: name, surname, adress,hobby
2. the new one have this structure: surname, payadress,

please give me some sugestion how can i do it

thanks a lot
N.

 

[MikeJones]

I'd create the old tables on the new DB (EXP/IMP) and then write migrate scripts to copy the data over. I'm not sure there is an easy way?

 

[never]

Thanks Mike for your answer but I can't use this..the database have compleatle diferent structure and both are created and I can't created new DB . I Can use only insert, select..and update and similar comands ..

I found this exmple but I don't undertand everything ...if you undertood ..can you help me

here it is..

SELECT 'INSERT INTO DIAGRAMS (ID, NAME) VALUES(' ||ID|| ','''||
NVL(RTRIM)NAME), NULL||''');'
FROM DIAGRAMS
ORDER BY ID;
---------------
WHAT'S ''' and || and exactly NVL

THX a lot
N.

 

[Nebuchednezzar]

This is a query which will generate an additional sql statement for you (a bunch of separate insert statements for all rows which could then be executed.
The || character means concatenate (put to values together.
In this case, it is taking the first string 'INSERT INTO DIAGRAMS (ID, NAME) VALUES ('
and concatenating it with the id selected from the diagrams table (the id is the first value to be inserted).
That is then concatenated with the second value NVL(RTRIM(NAME),NULL||''');
The syntax of nvl is NVL(FIELD,NEW_VALUE). Meaining...if the value of field is null, replace it with NEW_VALUE.
For example, if I run SELECT NVL(NULL,1) FROM DUAL;
I'll end up with 1 as the result.

oops, missed one...
the ''' is there to put a single quote (') in the return set.
normally, if you run...SELECT '''' FROM dual;
it will return '
In this case, the ''' is inside of another string like...
'insert into...'''...'. so it will return a single quote for you.

so...
take the example where your DIAGRAMS table looks like this...
ID NAME
1 BOB
2 WILBUR

If you run this statement...
SELECT 'INSERT INTO DIAGRAMS (ID, NAME) VALUES(' ||ID|| ','''||
NVL(RTRIM(NAME), NULL||''');'
FROM DIAGRAMS
ORDER BY ID;
you'll end up with these two rows returned...
INSERT INTO DIAGRAMS (ID, NAME) VALUES (1, 'BOB');
INSERT INTO DIAGRAMS (ID, NAME) VALUES (2,'');


Now...why they are using NVL the way they are confuses me a bit. The statement shown...
NVL(RTRIM)NAME), NULL||''');'
makes no sense to me in that the nvl is saying "if rtrim(name) is null, return null. Makes the nvl pointless.
also, in your post, it was listed as NVL(RTRIM)NAME)...it should be NVL(RTRIM(NAME)...
--notice the left parentheses between RTRIM and NAME.

Now, for the original question...how to get all the data from one db to another when the structures are different, this would work.
Write sql like the one discussed above which will create insert statements that can be spooled to a file as a script, then execute the script on the new database.

Another option...if you can link the two databases together, you can simply do an insert over a database link (well, in Oracle you can) like...
insert into table@new_db_link select column_1, column_2 from old_db_table
--substitute your table names and the database link name in.

 

[never]

Thanks Nebuchednezzar(perfect answer) ....no i understand complete ..and how can i get the data for insert script from table

 

[Nebuchednezzar]

Say you have a table on the old database with the structure...
ID NUMBER,
NAME VARCHAR2(20),
ADDRESS VARCHAR2(40)

and the structure of the table you are inserting into is...
ID NUMBER,
NAME VARCHAR2(30).

If you have a database link, you could just run...
INSERT INTO NEW_TABLE@DB_LINK (ID, NAME) SELECT ID, NAME FROM OLD_TABLE;

...or, to use sql to create sql insert statements...
SELECT 'INSERT INTO NEW_TABLE (ID, NAME) VALUES(' ||ID || ',' || '''' ||NAME|| '''' || ');' FROM OLD_TABLE;

 

[Sina]

You may want to try SQLPorter from realsoftstudio.com

Save yourself hours of work by in manual migration.
you can see them at

http://www.realsoftstudio.com