Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts

    Copying data in Oracle

    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;

  • #2
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •