on 02-15-2017 9:17 AM
Hi Expert,
We have a requirement where we need store the error records in a custom table with the entire records details.
For an example , I have a custom table with 15 rows out of them 5 are good records and 3 are bad records. I need to store this 3 bad records in another custom table .
We are using mass insert option the stored procedure . Is there any way to achieve this without using loop in store procedure?
Please suggest.
Thanks,
Girdhari
Hi,
That's still not clear Girdhari. The recommendation from Srikanth makes sense so please explain with details with you can't have intermediate result (for good and bad data) before inserting. So far I can't see why you'd need cursor here.
Regards,
Lucas de Oliveira
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Lukas,
Let me try to explain you the scenario .Suppose calculation view has 100 records but while inserting in physical table , insert got failed due to some error in some records (Like duplicate entry,data type mismatch etc). As per below insert statement complete insert will get failed and none of of the records gets inserted in the table, correct me if I am wrong.
insert into SAURAVSA.PRODUCT_NEW (Select * from "_SYS_BIC"."******.******.******/CV_PRODUCT")
But our requirement is not to fail the complete insert , the records which do not have any error should get inserted in the physical table and the records which has error should get inserted in another error table. But as per my understanding this can be only done using Loop which is not recommenced. Is there any other way to do this without using Loop? Hope I am able to explain you the scenario .
Thanks,
Girdhari
Hi Girdhari,
Difficult to understand what exactly you want to do here. Please elaborate on what you meant by "mass insert option" and provide a shorter version of your SP along with data for the source table and target table.
Regards,
Lucas de Oliveira
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Lukas,
Thanks for your reply.
We are using below statement to insert records from a calculation view into a physical table.
insert into SAURAVSA.PRODUCT_NEW (Select * from "_SYS_BIC"."******.******.******/CV_PRODUCT")
Now our requirement is if calculation view has 10 records and out of those 3 records has some error. We need to store the 3 error records in another error table with error message and remaining 7 success records needs to be updated in table PRODUCT_NEW.
I did some research and understand that , we have to use cursor or for loop to achieve this requirement but cursor and loop is not recommended in our project. Is there any other way to achieve this without using cursor or for loop?
Thanks,
Girdhari
Hi,
You may try something like this as split the "_SYS_BIC"."******.******.******/CV_PRODUCT as per your condition using where condition in to separate internal temp tables.
as Good_data = Select * from "_SYS_BIC"."******.******.******/CV_PRODUCT"
where column = condition
bad_data = Select * from "_SYS_BIC"."******.******.******/CV_PRODUCT" where not (column = condition)
And then use the good data in to existing to the
insert into SAURAVSA.PRODUCT_NEW (Select * from Good_data)
insert into SAURAVSA.PRODUCT_NEW_BAD (Select * from bad_data)
Let know how it goes.
User | Count |
---|---|
70 | |
26 | |
9 | |
9 | |
7 | |
6 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.