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?

Answer 1

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.

Answer 2

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))