CODEWITHSUNDEEP
sqloracle-databaseplsql
Femme Fatale
Femme Fatale

Reputation: 880

Copy data from one table to another- Oracle

I have two tables namely PERSON and WIFE. I want to make WIFE's data available in PERSON table while keeping entries of WIFE maintained and at the same time adding some the values of PERSON against the data of wife.

PERSON Table

    PK   NAME      ADDRESS    IS_MARRIED
    1  John        ab city     Y        
    2  Varvatos    cd town     N
    3  Smith       ef town     Y
    4  Henry       gh city     Y
    5  Lynda       gh city     Y

WIFE table

    PK  PERSON_ID (FK)    NAME         
    1    1                 Alice
    2    3                 Rosy
    3    4                 Lynda

Now i want to copy data of WIFE table into PERSON table like this
PERSON table

   PK   NAME      ADDRESS    IS_MARRIED
   1  John        ab city     Y        
   2  Varvatos    cd town     N
   3  Smith       ef town     Y
   4  Henry       gh city     Y
   5  Lynda       gh city     Y
   6  Alice       ab city     Y
   7  Rosy        ef town     Y

As in the given example you might have noticed that ADDRESS of wife is same as of her spouse and same goes for IS_MARRIED column. Moreover, the PK is also not duplicated. How to go about this?
*Edited*
Another important factor is Lynda already exits in PERSON table, therefore, i certainly don't want to duplicate her entry.

Upvotes: 1

Views: 20720

Answers (4)

user2001117
user2001117

Reputation: 3777

Hi Please try the below code: This meet your requirment

declare PKId number;
begin
  select nvl(max(person.pk),0) + 1 into PKId 
  from person;

  for x in (select w.Name, p.Address
            from wife w 
            inner join Person p on w.Person_id = P.pk
           ) loop

     insert into Person(pk, Name,Address,Is_Married) 
     values (PKId ,x.Name ,x.Address,'Y');

     PKId := PKId  +1;
  end loop;
  commit;
end

Upvotes: 0

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

declare
newId number;
begin
select nvl(max(person.pk),0) + 1 into newId from person;
for x in (
    select w.Name, p.Address
    from wife w inner join Person p
    on w.Person_id = P.pk) loop
   insert into Person(pk, Name,Address,Is_Married) values (newId ,x.Name ,x.Address,'Y');
   newId := newId  +1;
end loop;
commit;
end

Upvotes: 2

Art
Art

Reputation: 5782

Use CTAS-create table table_name as select whatever you need from both tables. Simply write a join and add create table as... above the select keyword. If you prefer insert as in Gordon's example and your table is big then you may add an Append hint to your insert...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Try this:

insert into Person(Name, Address, Is_Married)
    select w.name, p.address, 'Y'
    from wife w left outer join
         Person p
         on w.Person_id = person.pk

Upvotes: 0

Related Questions