Running a batch of SQL insert statements that involve linked tables (Oracle)

Question

I'm looking to populate data in a number of tables linked by foreign keys in one batch of insert statements.

My first statement looks like this...

Insert into SCH2.PEOPLE_MEDICAL_CONDITIONS (ID,PERSON_CODE,CONDITION,START_DATE,END_DATE,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_DATE,UPDATED_BY,IS_HEALTH_CARE_PLAN,IS_EMERGENCY_RESPONSE_PLAN,IS_ASCIA_PLAN,IS_HIGH_RISK,IS_ANAPHYLAXIS,IMPORTED_NOTES,DOCTOR_DETAIL_ID,DOCTOR_DIAGNOSED,STUDENT_HOSPITALISED_ALLERGY,HOSPITAL_NAME,REVIEW_DATE,USER_1,USER_2,USER_3,USER_4,USER_5,USER_6,USER_7,USER_8,USER_9,USER_10,USER_11,USER_12) values (PEOPLE_MEDICAL_CONDITIONS_SEQ.nextval,10014817,'Diabetes',to_date('01-JAN-00','DD-MON-RR'),null,'Y','MattOC.medtest',SYSDATE,null,null,'N','N','Y','N','N','Type 1',null,'Y','N','NNUH',null,null,null,null,null,null,null,null,null,null,null,null,null);

Because I want to auto generate ID's I'm using PEOPLE_MEDICAL_CONDITIONS_SEQ.nextval as the value in my ID field.

I then wish to immediately run another insert statement which has a foreign key to my first table (and insert statement)

Insert into SCH2.PEOPLE_MEDICINE (ID,PERSON_CODE,MEDICAL_CONDITION_ID,MEDICINE_NAME,DUE,ADMINISTRATION,ADMINISTRATION_DETAILS,DOSAGE,IS_LOW_STOCK,IS_DISPENSED_DAILY,START_DATE,END_DATE,EXPIRY_DATE,CREATED_BY,CREATED_DATE,UPDATED_DATE,UPDATED_BY,IMPORTED_NOTES,MED_TYPE,PARENT_PROVIDED,REVIEW_DATE,USER_1,USER_2,USER_3,USER_4,USER_5,USER_6,USER_7,USER_8,USER_9,USER_10,USER_11,USER_12) values (PEOPLE_MEDICINE_SEQ.nextval,10014817,(SELECT ID FROM PEOPLE_MEDICAL_CONDITIONS WHERE CONDITION = 'Diabetes' AND CREATED_BY = 'MattOC.medtest'),'Insulin','INS','DAILY','3 times daily','80 units','N','Y',to_date('01-JAN-00','DD-MON-RR'),null,null,'MattOC.medtest',SYSDATE,null,null,'Insulin',null,'N',null,null,null,null,null,null,null,null,null,null,null,null,null);

SCH2.PEOPLE_MEDICINE.MEDICAL_CONDITION_ID = SCH2.PEOPLE_MEDICINE.ID

Because I'm using the autoincrement function in the first insert statement, I've no way of knowing what the ID is to use in the MEDICAL_CONDITION_ID field in my second statement. I've resorted to this to identify the previously created record, which doesn't feel very clean...

(SELECT ID 
 FROM PEOPLE_MEDICAL_CONDITIONS 
 WHERE CONDITION = 'Diabetes' AND CREATED_BY = 'MattOC.medtest')

Because data is constantly going in and out of these tables, I'm reluctant to set actual values in the insert statements for the ID fields.

If I'm looking to insert data across multiple tables linked by foreign keys, whats the best way to reference a previous insert statement, the primary key for which needs to be in a following insert statement?

Many thanks,

user330315 · Accepted Answer · 2013-07-23 15:59:33Z

4

Because I'm using the autoincrement function in the first insert statement, I've no way of knowing what the ID is to use in the MEDICAL_CONDITION_ID field in my second statement.

Yes, you do know it. you can access the last generated value using the currval column of your sequence.

So just use PEOPLE_MEDICAL_CONDITIONS_SEQ.currval in the second insert statement. No need for another SELECT statement to retrieve the value.

You can find more details in the manual: http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns002.htm#i1009336

answered Jul 23, 2013 at 15:59

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Running a batch of SQL insert statements that involve linked tables (Oracle)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related