...

View Full Version : Copying data in Oracle



arnyinc
06-02-2004, 08:32 PM
I think this goes here since it's the same section as mysql...

I am trying to copy data from one table to another in different databases. Most of my tables are direct copies. This works just fine:

copy from username/password@instance1 to username/password@instance2 insert project (id,name) using select project_id, project_name from projects;

The problem comes in when I want to change my ugly database design. For example, the previous table structure used letters for some id's in smaller tables. Now I want it to be a number.

Example:
My "hardware type" table goes from

a=accessory
s=server
d=desktop
n=network device

to

1=accessory
2=server
3=desktop
4=network device

Now in my "hardware" table I want to do a "copy from" and convert all of the a's to 1's, the s's to 2's, the d's to 3's and the n's to 4's. Any suggestions?

copy from username/password@instance1 to username/password@instance2 insert hardware (id,name,type) using select hw_id, hw_name, hw_type from projects;

arnyinc
06-02-2004, 09:13 PM
I did an evil thing and asked someone at work and he helped me out. This is the answer for anyone who happens upon this thread.

select hw_id, hw_name, decode(hw_type, 'a', 1, 's', 2, 'd', 3, 'n', 4) from projects



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum