on 07-18-2007 8:41 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
9 | |
7 | |
3 | |
2 | |
2 | |
2 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.