Insert multiple lines into Informix

Question

I use a Informix bank and I have to do 3500 Inserts. Informix runs line by line and this is impractical. How do I insert all lines at once? Example:

insert into tabela (coluna1, coluna2, coluna3) values (valor1, valor2, valor3)
insert into tabela (coluna1, coluna2, coluna3) values (valorn, valorm, valorx)

I need to run these Inserts simultaneously.

Thank you

Answer 1

Unfortunately Informix does not have a DML command for insert on-mode batch/Bulk as in mysql .

Batch load options are from TXT file using delimiters (which by default, in Informix is pipe "|" but can use others such as ";" used in CSV standard).
All these options, normally the TXT file has to be accessible by the server where the database runs, because it is from it that the file will be accessed (even in the case of External table).

• EXTERNAL TABLE
  Where you can create a table that will read the TXT directly and so make a insert into ... select * from ...
• LOAD
  But it is only supported in the utility dbaccess which is a CUI version and normally run only on the server. Despite having some version of the client, I do not remember which, that it also comes installed.
• DBLOAD
  Data loading utility with transaction control. Also available only on the server.
• HPL
  Utility for high performance data loading. Useful only if you need to load millions of lines. Also available only on the server.

I know that in JDBC and ODBC there are functions for "Bulk Insert", but I understand that it should be specifically programmed in your code and aims to streamline the performance of the load process and not make your life easier.

You can refer to these commands in the online product manual at Informix 12.10 Knowledge center

If this load will only be done once, you can use SQL client programs to assist you, such as Squirrelsql . He owns a plugin called "dataimport" which allows you to load data from Excel files or in CSV format .

This plugin adds the Ability to import data from Excel or CSV files into a database using Squirrel.

Answer 2

I was able to solve it this way:

INSERT INTO tb_tabela (coluna1, coluna2, coluna3)
SELECT * FROM 
(
SELECT valor1, valor2, valor3 FROM SYSMASTER:SYSDUAL
UNION ALL
SELECT valorn, valorm, valorx FROM SYSMASTER:SYSDUAL
)

MERGE INTO tb_tabela AS tab
USING
(
SELECT valor1 as coluna1, valor2 as coluna2, valor3 as coluna3 FROM SYSMASTER:SYSDUAL
UNION ALL
SELECT valorn as coluna1, valorm as coluna2, valorx as coluna3 FROM SYSMASTER:SYSDUAL
)   AS query 
ON tab.id = query.id
WHEN MATCHED THEN UPDATE SET tab.coluna1 = query.coluna1, tab.coluna2 = query.coluna2, tab.coluna3 = query.coluna3