Copying Data from One Database to Another


I need to migrate data from old db to new one.  The issue is tables are little different.  


ID (varchar2) primary key,                               description(varchar2),  is_enabled(varchar2)
5aaf1ece-d19a-4fef-b041-280fe51e4769      a test one                  True


ID(number) primary key, name(varchar2), status(number), temp_uid(varchar2), created_date(date)

oldDB.category_list.ID --                newDB.category_list.temp_uid
oldDB.category_list.description --
oldDB.category_list.is_enabled --   newDB.category_list.status (if true, 1; if false, 0)

How do I approach this?

Thanks much.
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
There is no 'quick' one-size fits all.

Let's go from the old to the new:
On the original database server, you need an entry in the tnsnames.ora file that has the information for the new database server.

If the entry is called NEW then log in to sqlplus as system and issue:
create database link new connect to table_owner_in_new_database identified my some_password using 'NEW';

you should be able to the do:
select sysdate from dual@new;

If that works:
insert into new_table_name(col1,col2,col3) select col1,col2,col3 from old_table_owner.table_name;

of course, replace the table names and column names.

The reason I said to do it as system is most generic database users cannot create database links and you would have to grant permission to do so.

The choice is yours.
slightwv (䄆 Netminder) Commented:
If you can create a database link between the two just do in insert into as select statement.

If you cannot link them, you can either spool out the old data and use sql loader to load the data back in
create a table in the old database that looks like the new table then export/import
levbaoAuthor Commented:
Hi slightwv,

Thank you for you quick response.  Since I don't have much experience in Oracle, can you give me an example?  I am reading documentation how to use database link but don't get much of it.

Thank you very much.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

levbaoAuthor Commented:
Hi slightwv,

I just created a database link.  Is there a condition syntax that when inserting if oldDB.category_list.is_enabled true, newDB.category_list.status 1.

Thank you.
slightwv (䄆 Netminder) Commented:
>> if oldDB.category_list.is_enabled true, newDB.category_list.status 1.

If 'true' is a varcahr2 string then a simple case:

case when is_enabled='true' then 1 else 0 end
levbaoAuthor Commented:
Thank you so much, slightwv.
levbaoAuthor Commented:
Hi slightwv,

I've struggled to make this statement work:

insert into ssi_company_types@stp_07222013(name, status, temp_uid)
  select description, is_enabled, id from category_list@stp
  case when category_list.is_enabled='true'
  then ssi_company_types.status = 1
  else ssi_company_types.status = 0

I got an error
ORA-00933: SQL command not properly ended

Thank you.
slightwv (䄆 Netminder) Commented:
Correct syntax for a select is:
select col1, col2, col3 from table;

Your CASE statement is after the from...

I also don't think you need the other table link reference.

There are also issues with the case statement.

Maybe something like:
insert into ssi_company_types@stp_07222013(name, status, temp_uid)
  select description,
     case when category_list.is_enabled='true'
         then 1
         else  0
  from category_list;
levbaoAuthor Commented:
Again thank you very much for your lightning response.
slightwv (䄆 Netminder) Commented:
No problem.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.