You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Can any one tell me how to insert values from one table to another table using people code based on a condition on a field

11 replies [Last post]
ashok_star2004's picture
User offline. Last seen 1 year 33 weeks ago. Offline
Joined: 02/13/2009
Posts: 45

Can any one tell me how to insert values from one table to another table using people code based on a condition on a field.we cant use sqlexec as it returns only one row.

abhi10aug's picture
User offline. Last seen 4 years 39 weeks ago. Offline
Joined: 06/11/2008
Posts: 10
Re: Can any one tell me how to insert values from one table ...

You can use SQL object.
Say you want to insert data from table 1 to table 2.

 SQL1 = CreateSQL("Select Field1 , Field2 from ps_table1 where Field3 = :1",&Field3value);
 While &SQL1.Fetch (&fld1 , &fld2 )
 SQLExec("Insert into ps_table2 (Field1 , Field2) values (:1,:2)", (&fld1 , &fld2 ) ;
 End-while;

Ashar's picture
User offline. Last seen 3 years 51 weeks ago. Offline
Joined: 03/26/2008
Posts: 227
Re: Can any one tell me how to insert values from one table ...

Hi Ashok,

SQLExec returns only a single row when it is used to select but it can be used to insert more than one rows into the record.

Pseudo code would be,

SqlExec("Insert into Table_A Select FieldA, FIeld B, Field C from Table_B");

So it depends on you specific requirements.

Otherwise you can insert using Record Class.,

Local Record &rcdA, &rcdB;

&rcdA = CreateRecord(Record.RecordA);

&rcdA.KeyField1.Value = "X";
&rcdA.KeyField2.Value = "Y";

If &rcdA.SelectByKey() Then
&rcdB = CreateRecord(Record.RecordB);
&rcdB.CopyFieldsTo(&rcdA);
Else
REM Error Processing;
End-If;

Nitin's picture
User offline. Last seen 4 years 41 weeks ago. Offline
Joined: 06/30/2008
Posts: 73
Re: Can any one tell me how to insert values from one table ...

Give an example of the condition.. will try to use Setbased query..

ashok_star2004's picture
User offline. Last seen 1 year 33 weeks ago. Offline
Joined: 02/13/2009
Posts: 45
Re: Can any one tell me how to insert values from one table ...

Hi Ashar,

Thank You very much for your response.You helped me to get my task completed.Can you tell me about component interface dealing with level2 records.

Thanks in advance,
Ashok
....Have a nice day Ashar....

ashok_star2004's picture
User offline. Last seen 1 year 33 weeks ago. Offline
Joined: 02/13/2009
Posts: 45
Re: Can any one tell me how to insert values from one table ...

Hi Nitin,

I have a base table with employee details(email,phone,order,etc..)

I have another page with a secondary page.In this secondary page i made a radiobutton to search employees from base page by their email,phone,order number.if we enter them in a edit box,it should fetch them according to that search field.

These fetched data is to be inserted into a record on a grid.Then if we click on one row they should be displayed on d main page of this secondary page.Im having some pblm while copying and displaying them.
Can you help me up pls..

Thanks in advance,
Ashok

....Have a nice day Nitin....

Nitin's picture
User offline. Last seen 4 years 41 weeks ago. Offline
Joined: 06/30/2008
Posts: 73
Re: Can any one tell me how to insert values from one table ...

Make grid as NO auto select and click on the searh button do scrollselect and pass That parameter in WHERE clause and the You can select row(s) and can use CopyTo functionalty

Cheers
Nitin

Guest's picture
Guest (not verified)
Re: CI

hi all,
i am in to an complex situation,
probelm 1: i have created an bolt on page to get the details of an vendor in 9.1 PIA("as i dont want delieverd vendor page") all the data which are being entered in to this page is saved in an bolt on record cloned from an delieverd(as i need approval to enter the data directly in to delieverd table so i use an cloned table).now i want to transfer this data which are saved in bolt on cloned record to deleivered main table,how can i do it,by using CI or SQR, please help me with guidance,i prefer CI and i find VNDR_ID as a CI in which i need to do modififcation kindly help me if anyone of u did this before if possible with code.thanks

Hari_aindian's picture
User offline. Last seen 6 years 1 day ago. Offline
Joined: 08/02/2010
Posts: 84
Re: Can any one tell me how to insert values from one table ...

Guest: I need approval to enter the data directly in to delivered table so I use an cloned table
But you wanted to move the data to this table. Am not sure why you wanna use CI for this.
If the cloned records and the delivered record have the same fields (Key fields and all) then you can copy the values using copyto method. You can code this logic in the component level where you are having the bolt on page.

Regards,
Hari..A

Ashar's picture
User offline. Last seen 3 years 51 weeks ago. Offline
Joined: 03/26/2008
Posts: 227
Re: Can any one tell me how to insert values from one table ...

Dear Guest,
There is a delivered AE in FSCM which loads vendor data to tables.. i dont remember the name now.. its something like LOAD_VNDR..

Guest's picture
Guest (not verified)
Re: Can any one tell me how to insert values from one table ...

Hari thanks for the soloutin,
we have only very few fileds from the deleivered table to our cloned table,is it possible to use copy to function for the specific fields.but im sure that we just cloned the delievered record and removend unwanted fileds rest remains same,so can i use copy to, and is there any other soloution than CI i can use for this scenarion,please let me know,

Ashar,is it AP_CCR_VNDR_LOAD please let me know the exact name,im totally meessed up with this scenario,but with the help of you guys now i started to believe i can complete with your help,

thanks ashar and hari

Hari_aindian's picture
User offline. Last seen 6 years 1 day ago. Offline
Joined: 08/02/2010
Posts: 84
Re: Can any one tell me how to insert values from one table ...

Try using CopyFields function and let us know if it resolves your problem

Regards,
Hari.A