CREATE TABLE ACC (ID NOT NULL VARCHAR2(21), OWNER_ID VARCHAR2(21) , S_KEY__C VARCHAR2(15) , ACC_NUMBER VARCHAR2(50), NAME VARCHAR2(250), STATUS VARCHAR2(30) , CREATE_DT TIMESTAMP(6), UPDT_DT TIMESTAMP(6) ) alter table ACC add primark key(ID); Create table Organization ( ROW_ID NOT NULL VARCHAR2(15), POSTN_ID VARCHAR2(15) , LOC VARCHAR2(50) , NAME NOT NULL VARCHAR2(100), STAT_CD VARCHAR2(30) , TYPE_CD VARCHAR2(30), SF_ACCOUNTID VARCHAR2(21) ); Trigger: create or replace TRIGGER ACC_INS_UPD_TRG BEFORE INSERT OR UPDATE ON ACC FOR EACH ROW BEGIN IF INSERTING THEN :new.create_DT := sysdate; END IF; IF UPDATING THEN :new.updt_dt := sysdate; END IF; END; CREATE OR REPLACE FORCE VIEW ACC_VW ("ID", "OWNER_ID", "S_KEY__C", "ACC_NUMBER", "NAME", "STATUSC") AS select distinct dense_rank() over (order by row_id) as ID , null as owner_id, s.row_id as s_key__c, as acc_number, as name, s. Less details are mentioned in the post like insert statements, constraints etc. ORGANIZATION_SIE ( ROW_ID NOT NULL VARCHAR2(15) , POSTN_ID VARCHAR2(15) , LOC VARCHAR2(50) , NAME NOT NULL VARCHAR2(100), STAT_CD VARCHAR2(30) , OU_TYPE_CD VARCHAR2(30), SF_ACCOUNTID VARCHAR2(21) ); Now the requirement is to Insert data from ACC to ORGANIZATION by comparing ACC. ROW_ID I would like to write a stored procedure to Inserting new rows and updating existing rows from ACC to ORGANIZATION. Thanks, create or replace procedure acc_org_proc /*cursor c1 is select * from acc; rec c1%rowtype; begin open c1 loop fetch c1 into rec;*/ begin merge into organization rs using acc a on (a.s_key__c = rs.row_id) when matched then update set = a.name, rs.stat_cd = a.status__c rs.sf_accountid = a.accnumber wben not matched then insert values(id,name,stat_cd,sf_accountid) values(a.id,a.status__c,a.accnumber); exception when no_data_found then dbms_output.put_line('No data existed'); end; As suggested, you can use merge .I have seen your previous tips (Export data from SQL Server to Excel and Different Options for Importing Data into SQL Server) related to working with Excel and SQL Server data.Many of the situations end up translating to INSERT, UPDATE or DELETE code in one or more of our SQL Server databases.
In addition, the OPENROWSET command can also support SELECT statements where a table is joined to the Excel spreadsheet.Luckily enough, we can fake a staging table using DUAL: create table employees ( employee_id integer not null, employee_name varchar2(100) not null); alter table employees add constraint employee_pk primary key (employee_id); create or replace procedure merge_employee( i_emp_id in integer, i_emp_name in varchar2 ) is begin merge into employees e using (select i_emp_id id, i_emp_name name from dual) s on (e.employee_id = s.id) when matched then update set employee_name = when not matched then insert (employee_id, employee_name) values (s.id, s.name); end; / With the merge statement, we now have a single more complex query instead of the 3 we started with, or the two of the refined approach.A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table.1) Create the 'product' table and 'product_price_history' table Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.4)If you ROLLBACK the transaction before committing to the database, the data inserted to the table is also rolled back.