insert multiple rows
The poster asked whether Informix supports inserting several rows in one statement, as DB2/Oracle allow with INSERT INTO t (x,y) VALUES (1,2),(3,4),(5,6). Replies confirmed there is no direct equivalent. Suggested alternatives: INSERT ... SELECT from another table, INSERT cursors or array/bulk inserts in ESQL/C, simply issuing separate semicolon-separated INSERT statements, or on version 9.x a SELECT from a constructed TABLE(MULTISET/SET{ROW(...),ROW(...)}) collection — though that was offered as untested and no simpler than separate inserts.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
hi, is there a possibility to insert more than one row per statement as it is possible in oracle and db2? does a workaround exist? thanks for your help, martin
More than one row using what? SQL, ESQL/C, 4GL? In article <4ezk5.115$gG5.3861@nreader1.kpnqwest.net>, "Martin Zach" <martin.zach@paradine.at> wrote: > hi, > > is there a possibility to insert more than one row per statement as it is > possible in oracle and db2? does a workaround exist? > > thanks for your help, > martin > > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.
mars1972@my-deja.com wrote:
> More than one row using what? SQL, ESQL/C, 4GL?
>
> In article <4ezk5.115$gG5.3861@nreader1.kpnqwest.net>,
> "Martin Zach" <martin.zach@paradine.at> wrote:
> > hi,
> >
> > is there a possibility to insert more than one row per statement as it
> is
> > possible in oracle and db2? does a workaround exist?
Well in all engines:
INSERT INTO Foo SELECT * FROM Bar, Mug . . . ;
and in 9.X SQL:
INSERT INTO Foo
SELECT *
FROM TABLE(MULTISET{ROW('A','B',123...),
ROW('c',...),
.
}
);
You may need to fiddle with the syntax, but that's the basic idea.
Martin Zach wrote: > is there a possibility to insert more than one row per statement as it is > possible in oracle and db2? does a workaround exist? Can you outline the Oracle and DB2 facilities, so we can work out whether there are any alternatives in Informix. There is an array fetch mechanism, which is probably not directly relevant. There are INSERT cursors which may achieve more or less the result you're looking for. And there's the INSERT ... SELECT which is certainly available in both Oracle and DB2 as well as Informix, which answers the letter of your question, but probably not the spirit of it. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"
in db2 the insert statement looks like:
insert into table1 (x,y) values (1,2), (3,4), (5,6);
"Jonathan Leffler" <jleffler@informix.com> wrote in message
news:3993039D.2CD535E7@informix.com...
> Martin Zach wrote:
> > is there a possibility to insert more than one row per statement as it
is
> > possible in oracle and db2? does a workaround exist?
>
> Can you outline the Oracle and DB2 facilities, so we can work out
> whether there are any alternatives in Informix. There is an array fetch
> mechanism, which is probably not directly relevant. There are INSERT
> cursors which may achieve more or less the result you're looking for.
> And there's the INSERT ... SELECT which is certainly available in both
> Oracle and DB2 as well as Informix, which answers the letter of your
> question, but probably not the spirit of it.
>
> --
> Yours,
> Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
> "I don't suffer from insanity; I enjoy every minute of it!"
Martin Zach wrote:
> in db2 the insert statement looks like:
>
> insert into table1 (x,y) values (1,2), (3,4), (5,6);>
> "Jonathan Leffler" <jleffler@informix.com> wrote:
> > Martin Zach wrote:
> > > is there a possibility to insert more than one row per statement
> > > as it is possible in oracle and db2? does a workaround exist?
> >
> > Can you outline the Oracle and DB2 facilities [...]
OK, that's clear enough. There isn't a direct analogue of that.
You could probably use something fancy in version 9.21 which would
more or less do what you want, but it wouldn't be much simpler than
doing three separate insert statements:
-- Untested code
INSERT INTO table1(c, y)
SELECT * FROM TABLE(SET{ROW(1, 2), ROW(3,4), ROW(4,5)
::SET(ROW(x INTEGER, y INTEGER) NOT NULL));
--
Yours,
Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
"I don't suffer from insanity; I enjoy every minute of it!"
Martin Zach wrote:
>
> in db2 the insert statement looks like:
>
> insert into table1 (x,y) values (1,2), (3,4), (5,6);
No you cannot do this. You can, however, issue the three separate
inserts in a single submission semi-colon separated.
Art S. Kagel
> "Jonathan Leffler" <jleffler@informix.com> wrote in message
> news:3993039D.2CD535E7@informix.com...
> > Martin Zach wrote:
> > > is there a possibility to insert more than one row per statement as it
> is
> > > possible in oracle and db2? does a workaround exist?
> >
> > Can you outline the Oracle and DB2 facilities, so we can work out
> > whether there are any alternatives in Informix. There is an array fetch
> > mechanism, which is probably not directly relevant. There are INSERT
> > cursors which may achieve more or less the result you're looking for.
> > And there's the INSERT ... SELECT which is certainly available in both
> > Oracle and DB2 as well as Informix, which answers the letter of your
> > question, but probably not the spirit of it.
> >
> > --
> > Yours,
> > Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
> > Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
> > "I don't suffer from insanity; I enjoy every minute of it!"