Procedure to load data from one table to another table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Procedure to load data from one table to another table

  1. #1
    Join Date
    Jun 2002
    Posts
    10

    Procedure to load data from one table to another table

    I am using oracle9i database
    I have four tables in whcih i have to insert/update in one table

    1) Insert/Update Table " EMP"
    2) Temp Table " EMP_TEMP"
    3) EMP_ID "EMP_ID"
    4) DEPT "DEPT"

    1)The columns in first table is "EMP"

    EMP_ID DEPT_CODE SALARY RAISE_SALARY

    2)EMP_TEMP COLUMNS ARE

    EMPNO FIRST_CODE SECOND_CODE THIRD_CODE SALARY RAISE_SALARY

    3) EMP_ID Columns are

    EMP_ID EMPNO

    4) DEPT columns are

    CODE DEPT_CODE

    I need to load the data from EMP_TEMP to EMP
    1) New EMP_TEMP.EMPNO should be inserted, existing EMPNO updated IN "EMP" TABLE.
    2) Translate the EMPNO to the EMP_id from table "EMP_ID"
    If a EMPNO does not have a EMP_id, then do not load it.
    3) USE codes in the following order: Nvl(first_code, nvl(second_code, third_code))
    3.A) GET THE DEPT_CODE from table "DEPT" based on the above condition
    3.B) If DEPT_CODE is not available then insert nulL
    3.c) but dont update if DEPT_CODE IS NOT AVAILABLE
    4) Then SALARY,RAISE_SALARY AS IS based on EMP_ID

    Can we use merger statement to do all the above conditions

    any help is appreciated

    thanks

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    To insert and update to one table (EMP), you can use MERGE statement.

    For all other operations, use separate SQL statements.

    Tamil

  3. #3
    Join Date
    Jun 2002
    Posts
    10
    HI u are right

    I did same thing. I used merge statement to insert/update EMP table

    But after that to update table with DEPT_CODE i am not getting any idea how to do any help

    thanks for your help

Posting Permissions

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


Click Here to Expand Forum to Full Width