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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.