cancel
Showing results for 
Search instead for 
Did you mean: 

Inserting Multiple Records through SQL Action Block

Former Member
0 Kudos
258

Hi All,

I want to insert multiple records into database.

The solution I've is to use SQL Action block of BLS in loop and iterate it as many times as the number of records. But this will hit database call that many times.

Is there any other way to insert multiple records into database in single call ?

Thanks,

Sumit

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Sumit,

Error tracking will be an issue in case of bulk inserts. Like what Jeremy says, you will get a common error and you'll not be able to find out which insertion failed..

mySQL actually sopports bulk insert using the insert command itself.. There is no need of stored procedures... In case of MSMSQL and Oracle you have to write stored procedures for multiple inserts...

Hope this helps,

Ajitha

Former Member
0 Kudos

Thanks,

Is it possible to write Prepared Query in xMII ? If so, how ?

jcgood25
Active Contributor
0 Kudos

Ajitha you are wrong - a Command mode query template against either MSSQL or Oracle containing multiple statements is supported without the need for a procedure.

Please see the following thread for earlier discussion about multi-line Oracle statements:

For a safe sample of MSSQL against a Northwind connection:

INSERT INTO Region VALUES(1111, 'ABC123')

INSERT INTO Region VALUES(2222, 'DEF456')

Regards,

Jeremy Good

Former Member
0 Kudos

Jeremy,

I looked at the ACTIVEPORTAL\ORACLE\AlertLogInsert template, where multiple inserts are done.. but that happens in two different tables... in AlertLog and after a condition check, in AlertAckLog.. But is it possible that we have multiple insert statements like

INSERT INTO Region VALUES(1111, 'ABC123')

INSERT INTO Region VALUES(2222, 'DEF456')

in between BEGIN and END, with out any conditional check? I'm not yet sure... Please clarify....Even I have a scenerio like this and I'm at present looping through the single insert statement multiple times...I'm inserting multiple rows from a text file into database, the number of inserts depend on the number of rows in the file...

In that case how do I determine dynamically the number of inserts inside the query template

Sumit,

Sorry if I had driven you wrongly...

Thanks,

Ajitha

Former Member
0 Kudos

Thanks all,

But I want to make just one call to database to insert multiple records.

How can I do that ?

jcgood25
Active Contributor
0 Kudos

Sumit,

What database are you using and what attempts have you actually tried?

If you look at my previous post for the Northwind example it is just a matter of passing those two lines together to the Query parameter of a Command Mode SQLQuery (Ajitha why don't you actually try this - I know it works because in attempts to post accurate information I ran the query before pasting the syntax into the forum). If it is Oracle then use the BEGIN and END around the multiple statements and make sure each statement ends with a semicolon ( - MSSQL is much less stringent than Oracle.

In your BLS transaction just use a Local string property to build the multiple inserts into a long string and then link it to the Query parameter of your SQL action. The downside to this is that you have to hope that all of the inserts work since there is no easy way to pick up from the last failed insert. Maybe some sort of DTS package or database server mechanism for bulk loading your database table from a text file might be a better alternative to consider.

Maybe Confucius didn't say it, but "sometimes the best way to learn is to actually try and not expect forum answers served up on a silver platter."

Best Regards,

Jeremy Good

Former Member
0 Kudos

<b>-10</b> points for Jeremy. ;*)

I think this is actually a question for the database, not Xacute, xMII or the SQL Action Block.

If the database supports some method (i am unaware of) then the all of the above should be able to use it.

jcgood25
Active Contributor
0 Kudos

Depending upon your datatbase (MSSQL, Oracle, etc.) the Command mode query does support multiple lines in a single statement, but the syntax varies. The biggist problem with multi line statements is that you will only receive a single success/fail indicator and if a failure happens in the first INSERT statement the remaining INSERT/UPDATE/etc statements will be ignored. With Oracle you will need to use BEGIN and END around your multiple statements and make sure each individual statement ends with a semicolon.

Regards,

Jeremy

Former Member
0 Kudos

Hi,

Apart from that you can use Stored procedures also.

To call stored procedure from comand mode:

SQL - exec <Stored Procedure Name>

ORACLE - call <Stored Procedure Name> ( )

hope this helps.