Insert multiple rows in Informix

I was able to resolve it as follows:

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

Unfortunately Informix does not have a DML command for insert in bulk mode as in mysql.

The batch load options are from TXT file using delimiters (which by default in Informix is the pipe "|" but can use others, such as ";" used in the CSV standard). All of these options, normally the TXT file has to be accessible by the server where the database is running, since it will be the access to the file (even in the case of the external table ). >

• EXTERNAL TABLE
  Where you can create a table that will read TXT directly and thus make a insert into ... select * from ...
• LOAD
  However, it is only supported in the dbaccess utility which is a CUI version and usually only runs on the server. Although I have some version of the client, I can not remember which one, it also comes installed.
• DBLOAD
  Utility for data loading 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 speed up the performance of the loading process and not make life easier.

You can refer to these commands in the online product guide at Informix 12.10 knowledge center

If this load is only done once, you can use SQL client programs to help you, such as SquirrelSQL . It has a plugin called "dataimport" that allows you to load Excel or CSV format.

  

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