Yeah , its working. My mistake I over looked conversion error of fields. Thank you. Can you also suggest me about my NULL field error solution. ~Shyam
On Tuesday, February 18, 2014 5:22:40 PM UTC+5:30, Lukas Eder wrote: > > Hi Shyam, > > Have you had a thorough look at the manual? > > http://www.jooq.org/doc/3.3/manual/sql-building/sql-statements/insert-statement/#N112DB > > jOOQ does support the INSERT .. SELECT statement > > E.g. > > HierarchyLeaf HL = HIERARCHY_LEAF.as("hl"); > > DSL.insertInto(HIERARCHY_LEAF, > HIERARCHY_LEAF.ITEM_ID, > HIERARCHY_LEAF.HA_ID) > > .select( > > DSL.select(HL.ITEM_ID, > > HL.HA_ID) > > .from(HL) > > .join(...) > > .on(...) > > ) > > > Did you try the above? Why didn't this work? > > > > 2014-02-18 8:47 GMT+01:00 Sha <[email protected] <javascript:>>: > >> Thank you for you reply Lukas, >> Yeah i have gone through it several times. >> >> It would have been great if we have a facility to select some fields from >> one table and map them to other and insert. >> Here are the more details : >> For example >> Like in SQLServer, below query is possible. >> >> INSERT INTO hierarchy_leaf >> (item_id, >> ha_id >> ) >> SELECT hl.item_id, >> hl.ha_id >> FROM hierarchy_leaf hl >> inner join hlm >> on hl.ITEM_ID = hlm.item_id >> and hl.PERIOD_ID = hlm.period_id >> and hl.HA_ID = hlm.ha_id >> >> *The equalent is not possible in jOOQ and need to do in two step process >> as below which is very laborious.* >> *We need to iterate each and every row.* >> >> Result<Record4<Integer, Short>>* ae* = >> ctx.select(hl.ITEM_ID,hl.HA_ID).from*(hl*) >> .join*(TEMP_HLM*) >> .on(hl.ITEM_ID.equal(TEMP_HLM.ITEM_ID)) >> .and(hl.PERIOD_ID.equal(TEMP_HLM.PERIOD_ID)) >> .and(hl.HA_ID.equal( (Field<Short>) TEMP_HLM.field("HA_ID"))) >> .fetch(); >> >> for( int x = 0 ; x < ae.size() ; x++){ >> ctx.insertInto(TEMP_HIERARCHY_LEAF, >> TEMP_HIERARCHY_LEAF.ITEM_ID,TEMP_HIERARCHY_LEAF.HA_ID).values( >> * ae.get(x).getValue(TEMP_HIERARCHY_LEAF.ITEM_ID)* >> ,ae.get(x).getValue(TEMP_HIERARCHY_LEAF.HA_ID) >> ).execute(); >> } >> >> *Why dont make it easier? There is no straight forward approach, we need >> to get individual field by iteration and insert it. Which is cumbersome.* >> *Why cant we improve this ?* >> >> Thank you. >> ~Shyam >> >> On Monday, February 17, 2014 6:47:29 PM UTC+5:30, Lukas Eder wrote: >> >>> Hi Shyam, >>> >>> Did you see this section of the manual? >>> http://www.jooq.org/doc/3.3/manual/sql-building/sql- >>> statements/insert-statement/#N112DB >>> >>> What particular issues are you having with the INSERT .. SELECT API? How >>> could it be improved? >>> >>> Best Regards, >>> Lukas >>> >>> >>> 2014-02-17 12:21 GMT+01:00 Sha <[email protected]>: >>> >>>> Hi Lukas, >>>> I have scenario where I need to insert a few(specific) column fields >>>> from another table. >>>> Example >>>> >>>> INSERT INTO tbl_temp2 (fld_id) >>>> SELECT tbl_temp1.fld_order_id >>>> FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100; >>>> >>>> >>>> Is there any simpler way in jOOQ ? Current one mentioned in User guide >>>> pretty laborious. >>>> >>>> >>>> Thank you. >>>> >>>> ~Shyam >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "jOOQ User Group" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> >>>> For more options, visit https://groups.google.com/groups/opt_out. >>>> >>> >>> -- >> You received this message because you are subscribed to the Google Groups >> "jOOQ User Group" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected] <javascript:>. >> For more options, visit https://groups.google.com/groups/opt_out. >> > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
