CODEWITHSUNDEEP
sqloracle-database
Shiva Goud A
Shiva Goud A

Reputation: 322

How to insert two tables in a single query using oracle?

Here I want to insert data in two tables,

1. COMPONENT_MASTER

2. COMPONENT_TO_ACTION

For this I wrote two queries to insert.But there is a problem with two queries.

Queries are:

INSERT INTO COMPONENT_MASTER(COMPONENT_ID,ROW_ID,COMPONENT_IDENTIFICATION,COMPONENT_NAME,COMPONENT_TYPE,COMPONENT_STATE,FECHA_DE_CREACION,REVISION)
SELECT MAX(COMPONENT_ID) + 1,?,?,?,?,?,CURRENT_TIMESTAMP,? FROM COMPONENT_MASTER

2nd

insert into COMPONENT_TO_ACTION (ORDER_NUMBER,ACTION_ID,COMPONENT_ID,FECHA_DE_CREACION,GRABADO_POR,STATUS,REVISION) 
select max(ORDER_NUMBER) +1,?,?,current_timestamp,?,?,? from COMPONENT_TO_ACTION.

Both queries need to insert execute at a time. So both need to insert with same component_id.

Can I write a single Query to insert in two tables.

Upvotes: 0

Views: 107

Answers (1)

Michael Piankov
Michael Piankov

Reputation: 1997

regarding to your question multi table insert syntax look like:

INSERT ALL
into COMPONENT_MASTER(COMPONENT_ID,ROW_ID,COMPONENT_IDENTIFICATION,COMPONENT_NAME,COMPONENT_TYPE,COMPONENT_STATE,FECHA_DE_CREACION,REVISION)
              values (COMPONENT_ID,ROW_ID,COMPONENT_IDENTIFICATION,COMPONENT_NAME,COMPONENT_TYPE,COMPONENT_STATE,FECHA_DE_CREACION,REVISION)
into COMPONENT_TO_ACTION (ORDER_NUMBER,ACTION_ID,COMPONENT_ID,FECHA_DE_CREACION,GRABADO_POR,STATUS,REVISION) 
                  values (ORDER_NUMBER,ACTION_ID,COMPONENT_ID,FECHA_DE_CREACION,GRABADO_POR,STATUS,REVISION)
SELECT (select MAX(COMPONENT_ID) + 1 from COMPONENT_MASTER) as COMPONENT_ID,
       (select max(ORDER_NUMBER) + 1 from COMPONENT_TO_ACTION) as ORDER_NUMBER,
       ? as ACTION_ID,
       ? as GRABADO_POR,
       ? as STATUS
       ? as ROW_ID,
       ? as COMPONENT_IDENTIFICATION,
       ? as COMPONENT_NAME,
       ? as COMPONENT_TYPE,
       ? as COMPONENT_STATE,
       CURRENT_TIMESTAMP as FECHA_DE_CREACION,
       ? as REVISION 
  FROM DUAL;

but as guys advise you not to use select max(...) + 1. Oracle has special object to create unique consequent identifiers is sequence. And you cant use sequence in subquery with multitable insert, but you may add it into values(...) clause and use it with nextval and curval. Tom Kyte said that you may rely on insert order . Please change query to use it and in result you should get something like that:

-- for example I create two sequence 
CREATE SEQUENCE COMPONENT_MASTER_seq
START WITH     100000
INCREMENT BY   1
NOCACHE
NOCYCLE;

CREATE SEQUENCE COMPONENT_TO_ACTION_seq
START WITH     100000
INCREMENT BY   1
NOCACHE
NOCYCLE;


 INSERT ALL
into COMPONENT_MASTER(COMPONENT_ID,ROW_ID,COMPONENT_IDENTIFICATION,COMPONENT_NAME,COMPONENT_TYPE,COMPONENT_STATE,FECHA_DE_CREACION,REVISION)
              values (-- /*COMPONENT_ID*/
                       COMPONENT_MASTER_seq.nextval,ROW_ID,COMPONENT_IDENTIFICATION,COMPONENT_NAME,COMPONENT_TYPE,COMPONENT_STATE,FECHA_DE_CREACION,REVISION)
into COMPONENT_TO_ACTION (ORDER_NUMBER,ACTION_ID,COMPONENT_ID,FECHA_DE_CREACION,GRABADO_POR,STATUS,REVISION) 
                  values (--/*ORDER_NUMBER*/ 
                          COMPONENT_TO_ACTION_seq.nextval  ,ACTION_ID,
                          --/*COMPONENT_ID*/
                          COMPONENT_MASTER_seq.curval,FECHA_DE_CREACION,GRABADO_POR,STATUS,REVISION)
SELECT --COMPONENT_MASTER_seq.nextval as COMPONENT_ID, -- Oracle prohibit to use sequence with multi table insert
       --COMPONENT_TO_ACTION_seq.nextval as ORDER_NUMBER, -- Oracle prohibit to use sequence with multi table insert
       ? as ACTION_ID,
       ? as GRABADO_POR,
       ? as STATUS
       ? as ROW_ID,
       ? as COMPONENT_IDENTIFICATION,
       ? as COMPONENT_NAME,
       ? as COMPONENT_TYPE,
       ? as COMPONENT_STATE,
       CURRENT_TIMESTAMP as FECHA_DE_CREACION,
       ? as REVISION 
  FROM DUAL;

Upvotes: 1

Related Questions