change proc to merge instead of insert

Hi,
I have proc which i want to change to merge please see attachment i have marked
---------------------this change to merge
Business key for merge will be SIN.
Thanks


INSERT INTO employee

---------------------this change to merge
         INSERT INTO employee(DATE_CREATION,
                                 DATE_EXPIRY,
sam2929Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
I know you say you have a LOT of duplicates:  I would look at the INSERT INTO SELECT with a subselect or use the new
IGNORE_ROW_ON_DUPKEY_INDEX  hint.
https://docs.oracle.com/cloud/latest/db112/SQLRF/sql_elements006.htm#CHDEGDDG

Forget about loops/exceptions/commit counters/etc...

Either (this one hits the employee table twice):
INSERT INTO employee(DATE_CREATION,
	DATE_EXPIRY,
	DATE_LOADED,
	SIN,
	Region
)
select 
	TO_DATE (
		Z_DATE_AUDIT_BLOCK
		|| Z_TIME_AUDIT_BLOCK,
		'yyyymmddHH24MISS'),
	TO_DATE (Z_DATE_AUDIT_BLOCK, 'yyyymmdd'),
	LOAD_DATE,
	TO_NUMBER (SIN),
	TO_NUMBER (Region)
from STG_EMPLOYEE where sin not in (select sin from employee);

Open in new window

or

Note:  You need to replace whatever_the_emp_pk_index_is with the actual Primary Key index name
INSERT INTO employee /*+ IGNORE_ROW_ON_DUPKEY_INDEX(employee whatever_the_emp_pk_index_is) */
   (DATE_CREATION,
	DATE_EXPIRY,
	DATE_LOADED,
	SIN,
	Region
)
select
	TO_DATE (
		Z_DATE_AUDIT_BLOCK
		|| Z_TIME_AUDIT_BLOCK,
		'yyyymmddHH24MISS'),
	TO_DATE (Z_DATE_AUDIT_BLOCK, 'yyyymmdd'),
	LOAD_DATE,
	TO_NUMBER (SIN),
	TO_NUMBER (Region)
from STG_EMPLOYEE;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
You haven't posted enough information.  There is no attachment and not enough detail in the question itself.
0
 
sam2929Author Commented:
please see attached file
proc.docx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
I don't see the need for the in-memory temp tables.  I would do away with them.  If you actually need the commit point, there are other ways.  Implicit cursor loops for example.  Many times frequent commits can actually slow things down.

Just like your last question on this subject, I don't see an UPDATE statement so what are you hoping to achieve with a MERGE over an INSERT INTO SELECT statement?

There are some syntax errors in your sample procedure so this likely isn't 100% accurate but you should get the idea:
INSERT INTO employee(DATE_CREATION,
	DATE_EXPIRY,
	DATE_LOADED,
	SIN,
	Region
)
select 
	TO_DATE (
		Z_DATE_AUDIT_BLOCK
		|| Z_TIME_AUDIT_BLOCK,
		'yyyymmddHH24MISS'),
	TO_DATE (Z_DATE_AUDIT_BLOCK, 'yyyymmdd'),
	LOAD_DATE,
	TO_NUMBER (SIN),
	TO_NUMBER (Region)
from STG_EMPLOYEE;

Open in new window


Now if you have an UPDATE piece as well as an INSERT piece then a MERGE makes sense but I cannot say for sure since your sample code doesn't show it.
0
 
slightwv (䄆 Netminder) Commented:
While we wait for the reason for the MERGE.

Remember, I still would probably not do it this way.

Here is a cursor loop example without the temptab:
CREATE OR REPLACE PROCEDURE LOAD_EMPLOYEE
AS
   -- 
   v_error_message          VARCHAR2 (255);
   v_error_code             NUMBER;
   v_load_record_count      NUMBER := 0;
   v_load_commit_count      NUMBER := 1000;
   v_info_file_count        tmp_employee_data.file_record_count%TYPE;
   v_info_itc               tmp_employee_data.itc%TYPE;
   e_invalid_record_count   EXCEPTION;
--
BEGIN
   
   SELECT itc, file_record_count
     INTO v_info_itc, v_info_file_count
     FROM tmp_employee_data
    WHERE NAME = 'employee';   --

	for i in (SELECT * FROM STG_EMPLOYEE) loop

         	INSERT INTO employee(DATE_CREATION,
                                 DATE_EXPIRY,
                                 DATE_LOADED,
                                 SIN,
                                 Region
                                                             )
              VALUES (
                        TO_DATE (
                           i.Z_DATE_AUDIT_BLOCK
                           || i.Z_TIME_AUDIT_BLOCK,
                           'yyyymmddHH24MISS'),
                        TO_DATE (i.Z_DATE_AUDIT_BLOCK, 'yyyymmdd'),
			i.LOAD_DATE,
                        TO_NUMBER (i.SIN),
                        TO_NUMBER (i.Region)
                      );
         
        	 v_load_record_count := v_load_record_count + 1;

         	if mod(v_load_record_count, v_load_commit_count) = 0 then
			commit;
		end if;
	end loop;

   COMMIT;
END LOAD_EMPLOYEE;

Open in new window

0
 
sam2929Author Commented:
Reason for merge is we don’t want to insert duplicates based upon sin.

So we just want to load deltas

Other reason is loads are huge so deltas will reduce time
0
 
slightwv (䄆 Netminder) Commented:
I think there might be better ways but this should give you a start.

It is untested since I don't have your tables or data to test with but it should be close.

		MERGE into EMPLOYEE e using (
			select
                        TO_DATE (
                           i.Z_DATE_AUDIT_BLOCK
                           || i.Z_TIME_AUDIT_BLOCK,
                           'yyyymmddHH24MISS') c_Date,
                        TO_DATE (i.Z_DATE_AUDIT_BLOCK, 'yyyymmdd') a_date,
			i.LOAD_DATE,
                        TO_NUMBER (i.SIN) sin,
                        TO_NUMBER (i.Region) region
			from STG_EMPLOYEE
		) s on (s.sin=e.sin)
		when unmatched then insert (DATE_CREATION, DATE_EXPIRY, DATE_LOADED, SIN, Region)
			values( s.c_date, s.a_date, s.load_date, s.sin, s.region);

Open in new window


Using my loop code in a previous post, something like this would give you the new stuff:
      for i in (SELECT * FROM STG_EMPLOYEE where sin not in (select sin from employee)) loop
       ...
0
 
sam2929Author Commented:
is it possible to write cursor code with merge
0
 
sam2929Author Commented:
I want to modify original cursor using merge for now
0
 
slightwv (䄆 Netminder) Commented:
You are looping through a cursor row by row fetching the row into variables.  Then inserting the variables into a table.

A merge works with a cursor (the USING select statement).  You can probably write a MERGE statement by selecting the individual values you've fetched from DUAL.

BUT, think about what that would be doing.  It won't give you what you think it will give you.

The duplicate SIN is already in the cursor you've opened.  You have already fetched it into variables.  Then if you call MERGE, it will query DUAL and EMPLOYEE to see if the SIN exists or not.

This will actually ADD time to your code.  It would be faster to just do what you are likely doing:  Do the single row insert and ignore a DUP_VAL_ON_INDEX exception.
0
 
sam2929Author Commented:
k make sense so how can i add DUP_VAL_ON_INDEX exception.
0
 
slightwv (䄆 Netminder) Commented:
Nested PL/SQL blocks with an exception handler.

Using my example of implicit cursor loop:
CREATE OR REPLACE PROCEDURE LOAD_EMPLOYEE
AS
   -- 
   v_error_message          VARCHAR2 (255);
   v_error_code             NUMBER;
   v_load_record_count      NUMBER := 0;
   v_load_commit_count      NUMBER := 1000;
   v_info_file_count        tmp_employee_data.file_record_count%TYPE;
   v_info_itc               tmp_employee_data.itc%TYPE;
   e_invalid_record_count   EXCEPTION;
--
BEGIN
   
   SELECT itc, file_record_count
     INTO v_info_itc, v_info_file_count
     FROM tmp_employee_data
    WHERE NAME = 'employee';   --

	for i in (SELECT * FROM STG_EMPLOYEE) loop
            begin
         	INSERT INTO employee(DATE_CREATION,
                                 DATE_EXPIRY,
                                 DATE_LOADED,
                                 SIN,
                                 Region
                                                             )
              VALUES (
                        TO_DATE (
                           i.Z_DATE_AUDIT_BLOCK
                           || i.Z_TIME_AUDIT_BLOCK,
                           'yyyymmddHH24MISS'),
                        TO_DATE (i.Z_DATE_AUDIT_BLOCK, 'yyyymmdd'),
			i.LOAD_DATE,
                        TO_NUMBER (i.SIN),
                        TO_NUMBER (i.Region)
                      );
        
        	 v_load_record_count := v_load_record_count + 1;
             exception then dup_val_on_index then null;
            end;

         	if mod(v_load_record_count, v_load_commit_count) = 0 then
			commit;
		end if;
	end loop;

   COMMIT;
END LOAD_EMPLOYEE;

Open in new window


I would still look at a single statement using INSERT INTO SELECT.
0
 
sam2929Author Commented:
This code will not take care of updates it will just insert new rows i want to update too based upon business key if anything changed.
0
 
slightwv (䄆 Netminder) Commented:
>>This code will not take care of updates

That is correct.  I pointed that out 5 days ago in #a42499178

Your sample code in this question or the previous one doesn't have an update statement in it.  I cannot make up requirements for you.  I can take what you provide and offer advice on making it better.

There is a sample MERGE statement in #a42499216 that should give you a place to start.  You would just need to add the WHEN MATCHED UPDATE code.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.