CODEWITHSUNDEEP
oracle-databasejoininner-joinsql-insertoracle12c
Attila Balázs
Attila Balázs

Reputation: 59

insert all and inner join in oracle

I would like to insert data in to two tables. Will be one-to-many connection. For this, I have to use Foreign Key, of course.

I think, table1 - ID column is an ideal for this a Primary Key. But I generate it always with a trigger, automatically, every line. SO, How can I put Table1.ID (auto generated, Primary Key) column in to table2.Fkey column in the same insert query? 

INSERT ALL INTO table1 (   --here (before this) generated the table1.id column automatically with a trigger.
    table1.food,
    table1.drink,
    table1.shoe
) VALUES (
    'apple',
    'water',
    'slippers'
)
 INTO table2 (
    fkey,
    color
) VALUES (
    table1.id, -- I would like table2.fkey == table1.id this gave me error
    'blue'
) SELECT
    *
  FROM
    table1
    INNER JOIN table2 ON table1.id = table2.fkey;

The error message: "00904. 00000 - "%s: invalid identifier""

Upvotes: 1

Views: 168

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

Since you're using Oracle DB's 12c version, then might use Identity Column Property. Then easily return the value of first table's (table1) to a local variable by charging of returning clause just after an insert statement for table1, and use inside the next insert statement which is for table2 as stated below :

SQL> create table table1(
  2                      ID    integer generated always as identity primary key,
  3                      food  varchar2(50), drink varchar2(50), shoe varchar2(50)
  4                      );

SQL> create table table2(
  2                      fkey  integer references table1(ID),
  3                      color varchar2(50)
  4                      );

SQL> declare
  2    cl_tab table1.id%type;
  3  begin
  4    insert into table1(food,drink,shoe) values('apple','water','slippers' )
  5    returning id into cl_tab;
  6    insert into table2 values(cl_tab,'blue');
  7  end;     
  8  /

SQL> select * from table1;

ID  FOOD    DRINK   SHOE
-- ------- ------- -------
 1  apple   water  slippers

SQL> select * from table2;

FKEY COLOR
---- --------------------------------------------------
   1 blue

Anytime you issue the above statement for insertions between begin and end, both table1.ID and table2.fkey columns will be populated by the same integer values. By the way do not forget to commit the changes by insertions, if you need these values throughout the DB(i.e.from other sessions also).

Upvotes: 1

hotfix
hotfix

Reputation: 3396

As suggested by @OldProgrammer, use sequence

INSERT ALL INTO table1 (   --here (before this) generated the table1.id column automatically with a trigger.
    table1_id,
    table1.food,
    table1.drink,
    table1.shoe
) VALUES (
    <sequecename_table1>.nextval,
    'apple',
    'water',
    'slippers'
)
 INTO table2 (
    fkey,
    color
) VALUES (    
    <sequecename_table2>.nextval,
    <sequecename_table1>.currval, -- returns the current value of a sequence.
    'blue'
) SELECT
    *
  FROM
    table1
    INNER JOIN table2 ON table1.id = table2.fkey;

Upvotes: 2

Related Questions