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
4) DEPT columns are
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
To insert and update to one table (EMP), you can use MERGE statement.
For all other operations, use separate SQL statements.
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
Click Here to Expand Forum to Full Width