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