MVSFORUMS.com

pradeepg1 · Posted: Tue Mar 04, 2003 5:51 pm Post subject: Mass INSERT in a program

Can I use Mass INSERT statement in my application program like the one given here.

INSERT INTO TABLE A
SELECT * FROM TABLE B;

If I can use above statement in my program, what happens if one of the selected record from TABLE B is already present in TABLE A. Will the execution of the program stops by giving -803 SQL CODE without inserting remaining records or it will continue with inserting remaining records then throw the -803 error. Please share your thoughts.

Thanks,
Pradeep

pradeepg1 · Posted: Wed Mar 05, 2003 3:29 pm Post subject:

I just tested, it failed with -803 error and remaining records are not loaded. I thought it would discard the duplicates and continue with rest of the inserts like LOAD utility. But didn't workout.

gotluru · Beginner Joined: 17 Dec 2002 Posts: 29 Topics: 6

Try Like this:
INSERT INTO TABLE A
(SELECT * FROM TABLE B
where a.key <> b.key);

satjag · Beginner Joined: 19 Dec 2002 Posts: 19 Topics: 2

Is it not possible to handle the error code and continue the execution of the program, with suitable displays for the failed records ?
_________________
Regards,
satjag

Glenn · Beginner Joined: 23 Mar 2003 Posts: 56 Topics: 3

If this is within a program, I would suggest decoupling the statement. The mass insert is possible, but not really a good idea. Basically what I'm saying is to make the select statement a cursor and after you issue a fetch, issue a insert, and then take the appropriate actions based on the SQLCODE on the insert.

This also gives the ability for you to set up commits at appropriate times so the system doesn't grind to a halt if you have very many records and the command buffers get full over not getting emptied every so often...or a rollback doesn't take forever if you do have a problem.

Actually it is my belief that statements that operate on whole tables at once should not be included within programs for reasons like described...