CODEWITHSUNDEEP
oracle-databaseoracle11g
kamal
kamal

Reputation: 759

Fetch record from existing table and insert new record in Oracle

I have 2 tables xyz and abc. abc is child of xyz, there are two columns in xyz ie, xyz1, xyz2.

Also there are three columns in table abc, ie, xyz1, abc1, abc2.

I want to write a stored procedure to which I will pass two params. 

123, 456.

123 is the primary key of the record present in table xyz.

The query should fetch record of 123 from parent and child table and insert a new record having primary key 456 and rest of field is of record 123.

I am using Oracle 8i.

Upvotes: 0

Views: 214

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

All you need is two insert select statements:

create or replace procedure(vi_fromkey integer, vi_tokey integer) as
begin
  insert into xyz (xyz1, xyz2) select vi_tokey, xyz2 from xyz where xyz = vi_fromkey;
  insert into abc (xyz1, abc1, abc2) select vi_tokey, abc1, abc2 from abc where xyz = vi_fromkey;
end;

Upvotes: 1

Related Questions