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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
slightwv (䄆 Netminder) 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.