Home » SQL & PL/SQL » SQL & PL/SQL » Copy data from one table to another- Oracle (Oracle 11g)
Copy data from one table to another- Oracle [message #576997] Mon, 11 February 2013 12:18 Go to next message
ShahJee14
Messages: 4
Registered: December 2011
Location: Pakistan
Junior Member
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?
Another important factor is Lynda already exits in PERSON table, therefore, i certainly don't want to duplicate her entry.
Re: Copy data from one table to another- Oracle [message #576998 is a reply to message #576997] Mon, 11 February 2013 12:29 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


It would be helpful if you posted CREATE TABLE & INSERT statements so we can have your tables & data; too.
Re: Copy data from one table to another- Oracle [message #576999 is a reply to message #576997] Mon, 11 February 2013 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, feedback and thank people that help you and start with your previous topic, we are waiting for you...

Regards
Michel

[Updated on: Mon, 11 February 2013 12:45]

Report message to a moderator

Re: Copy data from one table to another- Oracle [message #577000 is a reply to message #576997] Mon, 11 February 2013 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As in the given example you might have noticed that ADDRESS of wife is same as of her spouse


If this is always true in your world, the model is wrong: nothing must be duplicated in a table.
Read Normalization.

Note that this site does not give solution to homework but just help you to learn.
Have a look at INSERT SELECT in Database SQL Reference.

Regards
Michel

[Updated on: Mon, 11 February 2013 12:35]

Report message to a moderator

Re: Copy data from one table to another- Oracle [message #577002 is a reply to message #576997] Mon, 11 February 2013 12:41 Go to previous messageGo to next message
ShahJee14
Messages: 4
Registered: December 2011
Location: Pakistan
Junior Member
Thanks Michel & BlackSwan. You both are really great help. This is what i really like about this forum. Smile
Re: Copy data from one table to another- Oracle [message #577004 is a reply to message #576997] Mon, 11 February 2013 12:58 Go to previous messageGo to next message
gaurav_katyal05
Messages: 4
Registered: February 2013
Location: India
Junior Member
insert into person 
(  select s2.nextval, name,( select p1.address from person p1 where p1.pk = w.person_id)
    ,'Y' 
    from wife w
    where w.name not in ( select name from person) 
 );



s2.nextval.....I have considered a sequence S2 from which primary key values are already been generated for the COLUMN "PK" in PERSON table.
Re: Copy data from one table to another- Oracle [message #577006 is a reply to message #577004] Mon, 11 February 2013 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@gaurav_katyal05

Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: Copy data from one table to another- Oracle [message #577007 is a reply to message #577006] Mon, 11 February 2013 13:09 Go to previous messageGo to next message
ShahJee14
Messages: 4
Registered: December 2011
Location: Pakistan
Junior Member
@Michel Cadot
You must be a fortune teller.

I wonder what made you conclude that my problem is an assignment.?
Re: Copy data from one table to another- Oracle [message #577009 is a reply to message #577007] Mon, 11 February 2013 13:28 Go to previous message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it is not then you need to take a SQL course as it is a basic SQL question.

Regards
Michel

[Updated on: Mon, 11 February 2013 13:28]

Report message to a moderator

Previous Topic: Table Creation Problem
Next Topic: Capture NO_DATA_FOUND from refcursor
Goto Forum:
  


Current Time: Sat Aug 30 04:08:08 CDT 2014

Total time taken to generate the page: 0.14481 seconds