insert into from union

any one been here before and have the answer? insert into tblname select stuff from tblname2 works OK select stuff from tblname2 union select stuff from tblname3 works OK But insert into tblname select stuff from tblname2 union select stuff from tblname3 Does not Any ideas? Mike P.S. IDS 10.00 UC5 on RHES4

From the IDS 10.00 Guide to SQL Syntax manual p2-403 & 2-404: Subset of SELECT Statement As indicated in the diagram for "INSERT" on page 2-395, not all clauses and options of the SELECT statement are available for you to use in an INSERT statement. The following SELECT clauses and options are not supported by Dynamic Server: - FIRST and INTO TEMP - ORDER BY and UNION Art On Wed, Oct 29, 2008 at 10:05 AM, MIKE SLAUGHTER < mike.slaughter@cognitomobile.com> wrote: > any one been here before and have the answer? > > insert into tblname > select stuff from tblname2> > works OK > > select stuff from tblname2 > union > select stuff from tblname3> > works OK > > But > > insert into tblname > select stuff from tblname2 > union > select stuff from tblname3> > Does not > > Any ideas? > > Mike > > P.S. IDS 10.00 UC5 on RHES4 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

But Mike you don't need what you're trying if this don't work insert into tblname select stuff from tblname2 union select stuff from tblname3 then simply do: insert into tblname select stuff from tblname2; insert into tblname select stuff from tblname3; and that will do... well not exactly 'cause it will not remove duplicates (union revome duplicates... or you may want to use union all). In case you still want to do what you want to do, what about: insert into tblname select stuff from ( select stuff from tblname2 union select stuff from tblname3 ) If this don't work on 10.00 (don't remember) that's a good excuse to move to 11.5 that support that indeed. ;) J. 2008/10/29 Art Kagel <art.kagel@gmail.com> > >From the IDS 10.00 Guide to SQL Syntax manual p2-403 & 2-404: > > Subset of SELECT Statement As indicated in the diagram for "INSERT" on page > 2-395, not all clauses and options of the SELECT statement are available > for > you to use in an INSERT statement. The following SELECT clauses and options > are not supported by Dynamic Server: > > - FIRST and INTO TEMP > > - ORDER BY and UNION > > Art > > On Wed, Oct 29, 2008 at 10:05 AM, MIKE SLAUGHTER < > mike.slaughter@cognitomobile.com> wrote: > > > any one been here before and have the answer? > > > > insert into tblname > > select stuff from tblname2> > > > works OK > > > > select stuff from tblname2 > > union > > select stuff from tblname3> > > > works OK > > > > But > > > > insert into tblname > > select stuff from tblname2 > > union > > select stuff from tblname3> > > > Does not > > > > Any ideas? > > > > Mike > > > > P.S. IDS 10.00 UC5 on RHES4 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do > those opinions reflect those of other individuals affiliated with any > entity > with which I am affiliated nor those of the entities themselves. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Or: insert into tblname select stuff from tblname2; insert into tblname select tblname3.stuff from tblname3 left outer join tblname on tblname.key = tblname3.key where tblname.somecol IS NULL; OR insert into tblname select tblname3.stuff from tblname3 where tblname3.key NOT IN (select tblname.key from tblname); Either query will eliminate the dups. Art On Wed, Oct 29, 2008 at 12:20 PM, Jean Sagi <jeansagi.ifx@gmail.com> wrote: > But Mike you don't need what you're trying > if this don't work > > insert into tblname > select stuff from tblname2 > union > select stuff from tblname3> > then simply do: > > insert into tblname > select stuff from tblname2> ; > insert into tblname > select stuff from tblname3> ; > > and that will do... well not exactly 'cause it will not remove duplicates > (union revome duplicates... or you may want to use union all). > > In case you still want to do what you want to do, what about: > > insert into tblname > select stuff > from (> > select stuff from tblname2> > union > > select stuff from tblname3 > )> > If this don't work on 10.00 (don't remember) that's a good excuse to move > to > 11.5 that support that indeed. ;) > > J. > > 2008/10/29 Art Kagel <art.kagel@gmail.com> > > > >From the IDS 10.00 Guide to SQL Syntax manual p2-403 & 2-404: > > > > Subset of SELECT Statement As indicated in the diagram for "INSERT" on > page > > 2-395, not all clauses and options of the SELECT statement are available > > for > > you to use in an INSERT statement. The following SELECT clauses and > options > > are not supported by Dynamic Server: > > > > - FIRST and INTO TEMP > > > > - ORDER BY and UNION > > > > Art > > > > On Wed, Oct 29, 2008 at 10:05 AM, MIKE SLAUGHTER < > > mike.slaughter@cognitomobile.com> wrote: > > > > > any one been here before and have the answer? > > > > > > insert into tblname > > > select stuff from tblname2> > > > > > works OK > > > > > > select stuff from tblname2 > > > union > > > select stuff from tblname3> > > > > > works OK > > > > > > But > > > > > > insert into tblname > > > select stuff from tblname2 > > > union > > > select stuff from tblname3> > > > > > Does not > > > > > > Any ideas? > > > > > > Mike > > > > > > P.S. IDS 10.00 UC5 on RHES4 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Art S. Kagel > > Oninit (www.oninit.com) > > IIUG Board of Directors (art@iiug.org) > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and > > do not reflect on my employer, Oninit, the IIUG, nor any other > organization > > with which I am associated either explicitly or implicitly. Neither do > > those opinions reflect those of other individuals affiliated with any > > entity > > with which I am affiliated nor those of the entities themselves. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.