Regarding Bulk insertion via Procedure by using Cursor

[former_member374631]

Hi All,

I am inserting records from one table to another table by using cursor in procedure. If any error occurs in that time, handling errors by using exit handler.

But the issue is, when i am using cursor to load the data of (17 lacks) the cursor is taking 20 mins time.

Can you please give me an advise, if any other way is there to inserting records or for increasing the performance.

Thanks

Rajeswari A.

[pfefferf]

The question is here, why you need a cursor and if it is not possible to fullfil your requirement using pure SQL.

But as you did not say or show anything about your required logic (are there any special transformations necessary, ...) and the amount of data to be processed, no advise can be given so far.

So please add some more details to your question.

Regards,
Florian

[former_member374631]

Here why i used cursor means, Handling all errors like if we have 50 records in the source table,while inserting into target table 20 records got failed, in this scenario i have to handle all these errors along with their primary key.

And I have to rollback reaming success records from source table. if there is no errors, then only the the source table having data.

Means i have to handle the all errors at a time, so that i used cursor.

If i used direct insert into target_table (select * from source_table),i am getting one one error only,But My requirement is i have to handle total errors at a time.And along with the error need to get primary key.

So Can you please guide me.

[former_member374631]

Thank you for reply...

Here while inserting if errors will occur i have to insert these errors into error table along with primary key of main table, what i am thinking this one is only possible with cursor.

Please see the below attached procedure.

CREATE PROCEDURE schemaname.procedure_name()

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

AS

BEGIN

/*****************************************************************************************

VARIABLE DECLARATION

******************************************************************************************/

DECLARE errcode INT;

DECLARE errmsg NVARCHAR(5000);

DECLARE l_validation_flag NVARCHAR(5);

DECLARE l_ndb_insrt_ts1 NVARCHAR(20);

DECLARE l_ndb_insrt_ts2 NVARCHAR(20);

DECLARE l_message NVARCHAR(5000);

DECLARE l_count INT;

DECLARE l_job_name NVARCHAR(1000) := 'proc_name';

/*****************************************************************************************

creating cursor

*****************************************************************************************/

DECLARE CURSOR C1 FOR SELECT * FROM table1;

FOR CUR_ROW AS C1 DO

/*****************************************************************************************

ERROR HANDLING

******************************************************************************************/

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN l_validation_flag := 'N';

errcode = ::SQL_ERROR_CODE;

errmsg = ::SQL_ERROR_MESSAGE ;

CALL erorr_procedure(:errcode, :errmsg, CUR_ROW.seq_id, CUR_ROW.cycl_id, l_job_name);

END;

/*****************************************************************************************

INSERTING RECORDS TO TABLE2

******************************************************************************************/

l_validation_flag := 'Y';

SELECT TO_NVARCHAR (CURRENT_DATE,'YYYYMMDD HHMMSS') INTO l_ndb_insrt_ts2 FROM dummy ;

INSERT INTO table1 VALUES (select * from table2);

/*****************************************************************************************

ROLLBACK AND UPDATING TABLES

******************************************************************************************/

IF l_validation_flag = 'N' THEN ROLLBACK;

ELSEIF l_validation_flag = 'Y' THEN

SELECT TO_NVARCHAR (CURRENT_DATE,'YYYYMMDD HHMMSS') INTO l_ndb_insrt_ts1 FROM dummy;

UPDATE table1 ;

END IF;

END IF;

END;

[BenedictV]

I dont think there is any other way you can do this, since you are handling exceptions at individual statement level execution.

[BenedictV]

What is the need to loop through records while you insert them to the target table?

If this is a simple source to target insert, you can use INSERT INTO <target> SELECT <fields> from SOURCE WHERE <filter condition>