0

I have 3 tables in my database and their schema looks something like, 

Table1 (Col1 Pk, Col2), 
Table2 (Col3 Pk, Col1 FK, Col4)
Table3 (Col5 Pk, Col3 FK, Col6)

Here the values of primary key columns is auto incremented on every insert operation. I want to insert values in the above tables. With the above scenario i cannot insert into Table3 unless i have value of Col3 (foreign key to Table2), similarly insert into Table2 won't go through unless i have value for Col1 (foreign key to Table1)

Question: How can i insert into the above tables in a single database connection ?

I can always make 3 different calls and insert values, but how can i use the value from Table1 and use it to insert into Table2, likewise value for Table2 and use it to insert into Table3 in single connection ?

I thought of writing a stored procedure but not sure how can i fetch Col1 value and use it to insert into Table2.

Pls suggest some thoughts here. An example will also help.

Thank you.

Vrushank Doshi
  • 2,428
  • 5
  • 20
  • 34
  • Possible duplicate: https://stackoverflow.com/questions/5558979/inserting-into-oracle-and-retrieving-the-generated-sequence-id – alans Mar 06 '19 at 23:36

1 Answers1

2

I think, you're looking for this. Just use Returning to retrieve the PK value and use the value on next insert

declare
    v_col1_id number(10) := null;
    v_col3_id number(10) := null;
begin
    insert into T1 (col2) values ('xxx') RETURNING col1 INTO v_col1_id; -- col1 - PK
    insert into T2 (col1, col4) values (v_col1_id, 'yyy') RETURNING col3 INTO v_col3_id; -- col3 - PK
    insert into T3 (col3, col6) values (v_col3_id , 'zzz'); -- col5 - PK
end;
/
T.S.
  • 18,195
  • 11
  • 58
  • 78
  • Don't do this. "RETURNING" is a non-standard SQL. – battlmonstr Mar 07 '19 at 18:26
  • @battlmonstr Do you have better way to do the same thing ? – Vrushank Doshi Mar 07 '19 at 19:10
  • I guess this is fine if you are ok to write a stored procedure anyway. Looking at [solutions here](https://stackoverflow.com/questions/9733085/auto-increment-for-oracle) they are all based on sequence / nextval anyway. Maybe auto-increment in general is not standard, and one have to use various proprietary extensions. – battlmonstr Mar 07 '19 at 19:27
  • @VrushankDoshi `Returning` is there for this. If you only used standard SQL, you would go nowhere. What you do, you write RDBMS-specific sql. You write standard when possible and when not possible, you separate. For example, in Sql Server same thing would be achieved by using `OUTPUT INSERTED`. So what we do in code, we just say `object.executeInsert` and these non-standard SQL bits are abstracted to the caller. – T.S. Mar 07 '19 at 19:31
  • @battlmonstr See comment ^^ above ^^. And then, what if this is Oracle12 and you just use `identity`? Trigger is more maintained. If this code needs to be executed via app, like .NET, you do as I said above. Abstract SQL calls, build sql generating objects or just use entity framework. – T.S. Mar 07 '19 at 20:00
  • You are right. I'm suprised that there's still no standard for such a basic thing in 2019. – battlmonstr Mar 07 '19 at 20:36
  • @battlmonstr In fact, I also support `MySql`. It doesn't have any native support for returning values besides inserted identity. That was a serious hustle to replicate returning values in .net code, so the call can be abstracted among 3 different engines. – T.S. Mar 07 '19 at 20:44
  • MySQL doesn't have RETURNING . This is MySQL approach: https://stackoverflow.com/questions/17112852/get-the-new-record-primary-key-id-from-mysql-insert-query – battlmonstr Mar 07 '19 at 21:08
  • @battlmonstr This is exactly what I said. But more over. In sql serv. and Ora, I can return not only PK/identity but **ANY** field. This is what I am talking about – T.S. Mar 07 '19 at 21:20