Home » SQL & PL/SQL » SQL & PL/SQL » How to insert new record into Table A using data from Table B (merged)
How to insert new record into Table A using data from Table B (merged) [message #260015] Fri, 17 August 2007 02:48 Go to next message
oracleram
Messages: 23
Registered: November 2006
Junior Member
Hi,
plesae help me that How can i populate a table from another table in PL/SQL,Thanx in advance...

Thanx & Regards,

Ram Nainar S
Re: How can i populate a table from another table in PL/SQL [message #260019 is a reply to message #260015] Fri, 17 August 2007 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why using PL/SQL?
Just use "insert ... select ..."

Regards
Michel
How to insert new record into Table A using data from Table B? [message #260038 is a reply to message #260015] Fri, 17 August 2007 04:20 Go to previous messageGo to next message
atgl
Messages: 25
Registered: March 2006
Junior Member
How do I populate/insert records into table A using data from another table B?

Table to be Populated: TABLE_CT_EOT, A is empty with below columns
RUN1#  Contract_No  No_of_Days, PJ_ID


Table to grab data: TABLE_CPIP_PROJECT, B with below columns partially (some not relevant column not mentioned)
Contract_No  EOT_DAYS, PJ_ID


FYI, I am in 8i environment.
A.contract_no = B.contract_no
A.no_of_days = b.no_of_days
A.pj_id = b.pj_id
Re: How to insert new record into Table A using data from Table B? [message #260042 is a reply to message #260038] Fri, 17 August 2007 04:32 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You want to INSERT records, not SELECT; columns to be joined (listed at the bottom of your message) are not relevant in this case. BTW, there's no 'b.no_of_days' column in 'TABLE_CPIP_PROJECT' table.

You didn't mention what value should 'run1#' have. A sequence?

So, use INSERT:
CREATE SEQUENCE seq;

INSERT INTO TABLE_CT_EOT
  (run1#, contract_no, no_of_days, pj_id)
  (select seq.nextval, contract_no, eot_days, pj_id
   from TABLE_CPIP_PROJECT
  );
Re: How to insert new record into Table A using data from Table B? [message #260049 is a reply to message #260042] Fri, 17 August 2007 04:53 Go to previous messageGo to next message
atgl
Messages: 25
Registered: March 2006
Junior Member
Actually I am running something like u but could u pls tell me what's wrong.

INSERT INTO TABLE_CT_EOT 
(contract_no, no_of_days, pj_id) 
values (Select p1.contract_no, p1.eot_days, p1.pj_id
        from TABLE_CPIP_PROJECT p1
        where p1.eot_days > 0)


I run in TOAD and giving me error:missing expression.
You may ignore the run# as I have set it seq.
Re: How to insert new record into Table A using data from Table B (merged) [message #260050 is a reply to message #260015] Fri, 17 August 2007 04:54 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You don't need the VALUES keyword.
Previous Topic: PLS-00306: wrong number or types of arguments in call to 'CREATE_CHANGE_SET'
Next Topic: auto genrated no
Goto Forum:
  


Current Time: Sun Dec 04 04:53:39 CST 2016

Total time taken to generate the page: 0.18161 seconds