How to do an insert with multiple rows in Informix SQL?

Question

I want to insert multiple rows with a single insert statement.

The following code inserts one row, and works fine:

create temp table mytmptable
(external_id char(10),
int_id integer,
cost_amount decimal(10,2)
) with no log;

insert into mytmptable values 
('7662', 232, 297.26);

select * from mytmptable;

I've tried changing the insert to this, but it gives a syntax error:

insert into mytmptable values 
('7662', 232, 297.26),
('7662', 232, 297.26);

Is there a way to get it working, or do I need to run many inserts instead?

Lukas Eder · Accepted Answer · 2014-08-04 13:56:38Z

8

You could always do something like this:

insert into mytmptable
select * 
from (
  select '7662', 232, 297.26 from table(set{1})
  union all
  select '7662', 232, 297.26 from table(set{1})
)

Pretty sure that's standard SQL and would work on Informix (the derived table is necessary for Informix to accept UNION ALL in INSERT .. SELECT statements).

edited Aug 4, 2014 at 13:56

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

answered Sep 9, 2012 at 23:40

Matt Hamilton

205k61 gold badges395 silver badges321 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jonathan Leffler Over a year ago

Informix requires a FROM clause with its SELECT statements.

Matt Hamilton Over a year ago

Oh wow. Ok - I'll leave this answer here in case it helps someone using a database other than Informix with a similar issue.

Highly Irregular Over a year ago

Even when I added a from clause, I could only get this working with a select, and not an insert. This is possibly because I'm using an additional layer on top of basic Informix functionality, so maybe that extra layer is the issue. It might work for others?

Community · Accepted Answer · 2017-05-23 11:54:47Z

7

As you found, you can't use multiple lists of values in a single INSERT statement with Informix.

The simplest solution is to use multiple INSERT statements each with a single list of values.

If you're using an API such as ESQL/C and you are concerned about performance, then you can create an INSERT cursor and use that repeatedly. This saves up the inserts until a buffer is full, or you flush or close the cursor:

$ PREPARE p FROM "INSERT INTO mytmptable VALUES(?, ?, ?)";
$ DECLARE c CURSOR FOR p;
$ OPEN c;
while (...there's more data to process...)
{
    $PUT c USING :v1, :v2, :v3;
}
$ CLOSE c;

The variables v1, v2, v3 are host variables to hold the string and numbers to be inserted. (You can optionally use $ FLUSH c; in the loop if you wish.) Because this buffers the values, it is pretty efficient. Of course, you could also simply use $ EXECUTE p USING :v1, :v2, :v3; in the loop; that foregoes the per-row preparation of the statement, too.

If you don't mind writing verbose SQL, you can use the UNION technique suggested by Matt Hamilton, but you will need a FROM clause in each SELECT with Informix. You might specify:

FROM "informix".systables WHERE tabid = 1, or
FROM sysmaster:"informix".sysdual, or
use some other technique to ensure that the SELECT has a FROM clause but only generates one row of data.

In my databases, I have either a table dual with a single row in it, or a synonym dual that is a synonym for sysmaster:"informix".sysdual. You can get away without the "informix". part of those statements if the database is 'normal'; the owner name is crucial if your database is an Informix MODE ANSI database.

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Sep 9, 2012 at 23:49

Jonathan Leffler

759k145 gold badges961 silver badges1.3k bronze badges

1 Comment

Highly Irregular Over a year ago

Thanks. I'm actually using a system that adds another layer on top of Informix, and had to resort to multiple inserts. Approx 1400 of them took about 2mins, but the temp table was persistent enough within the system that I didn't need to repeat it too often.

TCC · Accepted Answer · 2016-04-18 22:58:20Z

5

In some versions of Infomix you can build a virtual table using the TABLE keyword followed by a value of one of the COLLECTION data types, such as a LIST collection. In your case, use a LIST of values of Unnamed Row type using the ROW(...) constructor syntax.

Creating a TABLE from COLLECTION value http://www.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.sqls.doc/ids_sqs_1375.htm

ROW(...) construction syntax, for literals of Unnamed Row data type http://www.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.sqlr.doc/ids_sqr_136.htm

Example:

select * 
from TABLE(LIST{
  ROW('7662', 232, 297.26),
  ROW('7662', 232, 297.26)
}) T(external_id, int_id, cost_amount)
into temp mytmptable with no log

In the above, the data types are implied by the value, but when needed you can explicitly cast each value to the desired data type in the row constructor, like so:

ROW('7662'::char(10), 232::integer, 297.26::decimal(10,2))

edited Apr 18, 2016 at 22:58

answered Apr 18, 2016 at 22:47

TCC

2,6162 gold badges29 silver badges35 bronze badges

1 Comment

J Cracknell Over a year ago

Note that if you are parametrizing rows in a JDBC PreparedStatement you must explicitly cast each value, e.g. row(?::int, ?::text).

Joe R. · Accepted Answer · 2012-09-12 04:22:56Z

3

You can also insert multiple rows by storing the values in an external file and executing the following statement in dbaccess:

LOAD FROM "externalfile" INSERT INTO mytmptable;

However, the values would have to be DELIMITED by a pipe "|" symbol, or whatever you set the DBDELIMITER environment variable to be.

If you're using the pipe delimiter, the data in your external file would look like:

7662|232|297.26|
7663|233|297.27|
...

NOTE that the data in the external file must be properly formatted or able to be converted to successfully be inserted into each mytmptable.column datatype.

answered Sep 12, 2012 at 4:22

Joe R.

2,0624 gold badges38 silver badges78 bronze badges

Comments

Simas Joneliunas · Accepted Answer · 2020-05-21 02:20:22Z

1

Here is a simple solution fro bulk insert with SELECT part solving the rest

INSERT INTO cccmte_pp 
( cmte, pref, nro, eje, id_tri, id_cuo, fecha, vto1, vto2, id_tit, id_suj, id_bie, id_gru ) 
SELECT * FROM TABLE (MULTISET { 
row('RC', 4, 10, 2020, 1, 5, MDY(05,20,2020), MDY(05,20,2020),MDY(05,27,2020),101, 1, 96, 1 ), 
row('RC', 4, 11, 2020, 1, 5, MDY(05,20,2020), MDY(05,20,2020),MDY(05,27,2020),101, 1, 96, 1 ) }) 
AS t( cmte, pref, nro, eje, id_tri, id_cuo, fecha, vto1, vto2, id_tit, id_suj, id_bie, id_gru )
;

edited May 21, 2020 at 2:20

Simas Joneliunas

3,15620 gold badges32 silver badges39 bronze badges

answered May 20, 2020 at 21:48

pablo schmitt

611 silver badge1 bronze badge

1 Comment

pablo schmitt Over a year ago

no need special features like cursos or any other type or complex coding, loop over your ítems example in dapper , contruct the query and execute.. !, no need unión and select ,, ES MUY FEO ESO,,, elegant !:)

Collectives™ on Stack Overflow

How to do an insert with multiple rows in Informix SQL?

5 Answers 5

3 Comments

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related