ADO & SQL INSERT Statement

[avista]

Hi all,

Some one can help how to insert multiple rows with one SQL command
in INFORMIX database

(Otherwise it is so slow inserting rows one by one)

Best Regards,

[Baxajaun]

Hi,

can you try this http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2013/02/28/insert-multiple-values-sets-into-a-table-in-one-command.aspx ?

http://sqlstudies.com/2013/02/28/insert-multiple-values-sets-into-a-table-in-one-command/

Regards

[avista]

Baxajaun,
Tahnks for reply

This SQL produce Syntax Error

Code: Select all  Expand view

INSERT INTO Table (field1, field2) VALUES ('string1','string2'), ('stringA','stringB')

Best regards,

[reinaldocrespo]

Avista;

Most SQL engines can execute a prepared query, also known as a unnamed parameters in SQL. When such SQL executes it is cached by the server and optimized to be executed many times. Each time you execute the SQL you'd only need to change the values for the parameters.

Example:
INSERT INTO Table1 VALUES ( ?, ?, ? ) ;

There are also named parameters. Example:
INSERT INTO Table1 VALUES ( :cName, :cAddress, :dDOB ) ;

After "preparing" this SQL you can now execute much faster inside a for loop many times changing the values each time. Each SQL will vary its syntax. Can't help with Informix, but ADS uses a set of functions from ACE32.dll named ADSSet....() to set each parameter. Perhaps you can do a search for named parameters and/or unnamed parameters with Informix.

Hope that helps.


Reinaldo.

[Baxajaun]

Hi Avista,

i've tried in my sqlite installation the following code

Code: Select all  Expand view

insert into test  (campo1,campo2)  values ('1','texto1'),('2','texto2')

and no error.

My table test structure:

campo1 numeric
campo2 text

Can you put here your error ?

Best regards

[Marcelo Via Giglio]

Holas,

usually an INSERT statement add one row only, but if the data source is part of the other table
or tables you can combine INSERT WITH SELECT in the same statement

INSERT INTO <table> SELECT <rows,static data,...> FROM <table> WHERE <cond>

All depend of the scenario

best regards

Marcelo

[nageswaragunupudi]

Hi Avista,

i've tried in my sqlite installation the following code

Code: Select all  Expand view

insert into test  (campo1,campo2)  values ('1','texto1'),('2','texto2')

and no error.

My table test structure:

campo1 numeric
campo2 text

Can you put here your error ?

Best regards

Many servers support multiple row inserts this way or similar way.
(Note: FW_AdoImportFromDBF() uses the syntax appropriate for different servers using multiple row insert statements)

Informix does not support.
We need to do single row inserts only.
Parameterized queries as suggested above will improve the speeds.

[avista]

Thanks to all for reply

INSERT INTO <table> SELECT <rows,static data,...> FROM <table> WHERE <cond>

This is not problem but problem is to INSERT data from .TXT or .DBF file.

i've tried in my sqlite installation the following code
CODE: SELECT ALL EXPAND VIEW
insert into test (campo1,campo2) values ('1','texto1'),('2','texto2')
and no error.

But like Rao said,

Informix does not support.
We need to do single row inserts only.

Statements UNLOAD TO and LOAD FROM are not standard SQL statements and they are part only in IMB DbAccess

I fond this on IBM info center

IBM Informix ODBC Driver Programmer's Manual
Inserting Multiple Rows
Use an insert cursor to efficiently insert rows into a table in bulk. To create an insert cursor, set the SQL_ENABLE_INSERT_CURSOR attribute using SQLSetStmtOption, then call SQLParamOptions with the number of rows as a parameter. You can create an insert cursor for data types VARCHAR, LVARCHAR, and opaque.

When you open an insert cursor, a buffer is created in memory to hold a block of rows. The buffer receives rows of data as the program produces them; then they are passed to the database server in a block when the buffer is full. The buffer reduces the amount of communication between the program and the database server. As a result, the insertions go faster.

Probably i need to find more informations how to CREATE INSERT CURSOR and use it.
If someone have experience with this please share

May be not bad idea FiveWin team to implement using of INSERT CURSOR

Thanks to all,
Best regards,

[nageswaragunupudi]

This is a very useful information. This can be improved upon.

Also every provider provides a way to bulk import from text data. That's the fastest way. You may also check on this.

[avista]

Rao,

I found this how to use INSERT CURSOR but still not have idea how to implement in program ... i will try this days
and please if you have some experiance with this tell me some sugestions

IBM Informix Guide to SQL: Tutorial

Using an Insert Cursor
The DECLARE CURSOR statement has many variations. Most are used to create cursors for different kinds of scans over data, but one variation creates a special kind of cursor, called an insert cursor. You use an insert cursor with the PUT and FLUSH statements to efficiently insert rows into a table in bulk.

Declaring an Insert Cursor
To create an insert cursor, declare a cursor to be for an INSERT statement instead of a SELECT statement. You cannot use such a cursor to fetch rows of data; you can use it only to insert them. The following 4GL code fragment shows the declaration of an insert cursor:

DEFINE the_company LIKE customer.company,
the_fname LIKE customer.fname,
the_lname LIKE customer.lname
DECLARE new_custs CURSOR FOR
INSERT INTO customer (company, fname, lname)
VALUES (the_company, the_fname, the_lname)
When you open an insert cursor, a buffer is created in memory to hold a block of rows. The buffer receives rows of data as the program produces them; then they are passed to the database server in a block when the buffer is full. The buffer reduces the amount of communication between the program and the database server, and it lets the database server insert the rows with less difficulty. As a result, the insertions go faster.

The buffer is always made large enough to hold at least two rows of inserted values. It is large enough to hold more than two rows when the rows are shorter than the minimum buffer size.

Inserting with a Cursor
The code in the previous example prepares an insert cursor for use. The continuation, as the following example shows, demonstrates how the cursor can be used. For simplicity, this example assumes that a function named next_cust returns either information about a new customer or null data to signal the end of input.

EXEC SQL BEGIN WORK;
EXEC SQL OPEN new_custs;
while(SQLCODE == 0)
{
next_cust();
if(the_company == NULL)
break;
EXEC SQL PUT new_custs;
}
if(SQLCODE == 0) /* if no problem with PUT */
{
EXEC SQL FLUSH new_custs; /* write any rows left */
if(SQLCODE == 0) /* if no problem with FLUSH */
EXEC SQL COMMIT WORK; /* commit changes */
}
else
EXEC SQL ROLLBACK WORK; /* else undo changes */
The code in this example calls next_cust repeatedly. When it returns non-null data, the PUT statement sends the returned data to the row buffer. When the buffer fills, the rows it contains are automatically sent to the database server. The loop normally ends when next_cust has no more data to return. Then the FLUSH statement writes any rows that remain in the buffer, after which the transaction terminates.

Re-examine the INSERT statement on page ***. The statement by itself, not part of a cursor definition, inserts a single row into the customer table. In fact, the whole apparatus of the insert cursor can be dropped from the example code, and the INSERT statement can be written into the code where the PUT statement now stands. The difference is that an insert cursor causes a program to run somewhat faster.

Status Codes After PUT and FLUSH
When a program executes a PUT statement, the program should test whether the row is placed in the buffer successfully. If the new row fits in the buffer, the only action of PUT is to copy the row to the buffer. No errors can occur in this case. However, if the row does not fit, the entire buffer load is passed to the database server for insertion, and an error can occur.

The values returned into the SQL Communications Area (SQLCA) give the program the information it needs to sort out each case. SQLCODE and SQLSTATE are set to zero after every PUT statement if no error occurs and to a negative error code if an error occurs.

The database server sets the third element of SQLERRD to the number of rows actually inserted into the table, as follows:

Zero, if the new row is merely moved to the buffer
The number of rows that are in the buffer, if the buffer load is inserted without error
The number of rows inserted before an error occurs, if one did occur
Read the code once again to see how SQLCODE is used (see the previous example). First, if the OPEN statement yields an error, the loop is not executed because the WHILE condition fails, the FLUSH operation is not performed, and the transaction rolls back. Second, if the PUT statement returns an error, the loop ends because of the WHILE condition, the FLUSH operation is not performed, and the transaction rolls back. This condition can occur only if the loop generates enough rows to fill the buffer at least once; otherwise, the PUT statement cannot generate an error.

The program might end the loop with rows still in the buffer, possibly without inserting any rows. At this point, the SQL status is zero, and the FLUSH operation occurs. If the FLUSH operation produces an error code, the transaction rolls back. Only when all inserts are successfully performed is the transaction committed.

May be not bad idea FiveWin team to implement using of INSERT CURSOR

Best regards,