| Author |
Message
|
| prasadpav |
 Posted: Thu Jan 19, 2006 1:57 am Post subject: Inserting multiple rows using one Insert statement |
 |
|
Centurion
Joined: 03 Oct 2004
Posts: 142
|
Hi,
I'm using Message Broker V6 on AIX and oracle 9i as database for storing application data. I've a repeating element in my XML, each repeating element must be inserted as a row in the database. Because of volume of messages and number of rows to be inserted, is there a way to Insert multiple rows into database using single insert? I didn't used DataInsert node and i'm not sure of DataInsert node inserting each row individually or in a single insert.
Thanks,
Prasad |
|
| Back to top |
|
 |
| mgk |
| Posted: Thu Jan 19, 2006 7:43 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1647
|
I'm sorry to say that you cannot insert multiple rows with an esql select.
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| prasadpav |
| Posted: Thu Jan 19, 2006 8:44 am Post subject: |
|
|
Centurion
Joined: 03 Oct 2004
Posts: 142
|
MGK, Thanks for the response.
Is this limitation just for Oracle database or all databases??? Also, is it a ODBC driver limitation or ESQL?
Others,
Anyone encountered this in any of your projects and any ideas of efficiently solving this?? I'm interested for a solution for oracle 9i.
Thanks alot,
Prasad |
|
| Back to top |
|
|
| fschofer |
| Posted: Thu Jan 19, 2006 9:59 am Post subject: |
|
|
Knight
Joined: 02 Jul 2001
Posts: 524
Location: Mainz, Germany
|
Hi,
i think this is a SQL limitation (not ESQL or ODBC) .
So far i know you can only perform multiple inserts
by selecting from on table to another.
look here
http://www.techonthenet.com/sql/insert.php
Use a loop inside a compute node and insert line by line.
Greetings
Frank |
|
| Back to top |
|
|
| EddieA |
| Posted: Thu Jan 19, 2006 10:55 am Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
I'm fairly sure you can insert multiple rows using SQL. In which case, you can build the SQL statement, and then use PASSTHRU.
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Thu Jan 19, 2006 9:00 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi prasadpav,
| Quote: |
| i'm not sure of DataInsert node inserting each row individually or in a single insert |
It can insert mutliple rows for each occurance of your tag.
You will find the solution if you search the forum.
Regards. |
|
| Back to top |
|
|
| nathanw |
| Posted: Tue May 09, 2006 2:50 am Post subject: |
|
|
Knight
Joined: 14 Jul 2004
Posts: 550
|
| surely if there is an unknown number of inserts then the cardinality of the input can be used within a while loops to insert each time that it is required? |
|
| Back to top |
|
|
|
|
