How to insert with multiple rows in Informix SQL?

Question

I want to insert multiple lines into a single insert statement.

The following code inserts one line 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 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 make it work, or do I need to run many inserts instead?

Answer 1

As you find, you cannot use multiple lists of values ​​in the same INSERT statement with Informix.

The simplest solution is to use several INSERT statements, each of which contains one list of values.

If you use an API such as ESQL / C and you are concerned about performance, you can create an INSERT cursor and reuse it. This saves the inserts until the buffer is full, or you close or close the cursor:

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

Variables v1 , v2 , v3 are host variables for storing strings and numbers to be inserted. (If necessary, you can use $ FLUSH c; in a loop.) Since it buffers values, it is pretty efficient. Of course, you could just use $ EXECUTE p USING :v1, :v2, :v3; in the loop; which also precedes the report for each row.

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

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

In my databases, I have either a dual table with one row in it, or a synonym for dual , which is synonymous with sysmaster:"informix".sysdual . You can do without "informix". parts of these operators if the database is "normal"; owner name is crucial if your database is an ANSI Informix MODE database.

Answer 2

You can 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}) ) 

I’m pretty sure that standard SQL will work on Informix (a view is required for Informix to accept UNION ALL in INSERT .. SELECT ).

Answer 3

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

 LOAD FROM "externalfile" INSERT INTO mytmptable; 

However, the values ​​MUST refer to the pipe "|" character or what you set for the DBDELIMITER environment variable.

If you use a channel separator, the data in your external file looks like this:

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

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

Answer 4

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

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(...) build syntax, for literals like Unnamed Row 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 example, data types are implied by a value, but if necessary, you can explicitly pass each value to the desired data type in the string constructor, for example:

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