bulk inserts from stored procedure

  • Permalink
  • submit to reddit
  • Email
  • Follow


What is the best way to do bulk inserts from a SQL stored procedure or
one created by using C with embedded sql and host variables?

I need to call it from VB via ODBC

Does DB2 SQL handle arrays yet?

If anyone could point me to some examples that would be great!

Thanks in advance!

0
Reply spoliskey (49) 11/30/2005 10:03:16 PM

See related articles to this posting

comp.sys.ibm.as400.misc 9047 articles. 12 followers. Post

0 Replies
200 Views

Similar Articles

[PageSpeed] 13


Reply:

Similar Artilces:

Stored Procedure to Insert OR Update
Hello I have a simple stored procedure that takes couple of values and inserts a new record using them into a table. However, if the primary key, is duplicated it throws an error and doesn't insert anything. Good so far. However is it possible to change that SP so that if the record being inserted has a primary key that already exists in table, it treats is as an UPDATE and now searches for the primary key and updates the record with the new entries. Also is it possible to just UPDATE columns in the record that are different from the ones already in the table? Thanks for any help or a...

Insert Query with stored procedure
Hi, is it possible to create an "INSERT INTO ..... "Select from stored procedure" Query? I want to create an temporary table. In this table I want to enter the data, which I can get from an stored procedure. But in the FROM-clause a stored procedure is not allowed? "Harald" <yixxu@yahoo.de> wrote in message news:bfhh70$jt5$1@online.de... > Hi, > > is it possible to create an "INSERT INTO ..... "Select from stored > procedure" Query? > > I want to create an temporary table. In this table I want to enter the data, > wh...

Stored procedure Bulk replace
Hi all! I have the need to replace all occurrences of a string in all stored procedures of one user. is it possible to do it ? in practice we have an Oracle based application which uses Oracle cartidge to produce html Since Oracle cartridge will be dismissed we have to change all the cartridge Api with some other instructions. In this example : CREATE OR REPLACE PACKAGE BODY dummy AS ..........) IS BEGIN htp.p('<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional// EN">'); htp.p('<html><head><title>WIND R.D.C.P. MENU</title></head...

LARGE INSERT in STORED PROCEDURE
Hi, I am trying to insert a large number of rows into a table inside a SPL. But every time, I run the SPL, the table is locked because of the INSERT. When I tried to issue a COMMIT, right after the INSERT in the SPL. I got an error stating that there was no open cursor. I have done some research on here and found that it's not possible to issue a COMMIT inside an SPL. But it can be done so on the client side. Can anyone shade me some light on this ? I'm quite new here and not sure how to do that. What are other options to get around this if I have to commit after each ...

how to call a stored procedure in an insert command
hi, i had a small doubt , i have a table xxx with two columns (a int,b int ) and i have inserted 5 rows my query is to add the two colums using a stored procedure and the result has to be displayed in an separate column --this has to be done only stored procedures ---i know how to solve the problem using computed columns concept like this create table yyy(a int ,b int ,total as a+b) insert into yyy values (12,13) select * from yyy i need the answer using stored procedures---is it possible pls help me satish create proc get_your_calc as begin select a, b, total = a + b from yyy ...

INSERT or UPDATE
Hello, I've got sql statement: update lib/t1 set PRLT = (select detmin from lib/t2 where kod = prdc) where prdc in (select kod from lib/t2) and UNIT = 'SZT' -- and second one insert into lib/t1 ( select kod, 'PLN' , 'SZT' , detmin from ( select * from lib/t2 left outer join lib/t1 on prdc = kod ) z where prdc is NULL ) as You can imagine my point is insert not existing records (into t1) from t2 and update existing. SQL solution is ok, but looking for more elegan way I've found RPG useful (basically CHAIN opcode and WRITE if NOT %FOUND). No...

insertion using stored procedure in oracle.
hi there, I need to write to a stored procedure in oracle 9i to insert values in a table in which: 1) Values in one column are to be retrived from a column in another table. 2)Getting three parameters as inputs in the procedure,and inserting those values in the same row which has the value that has been taken from the different table. am very new to sp and am really confused when do we use wat..like cursors,index,functions etc. so plzz provide some sample code to handle this insertion. This sounds like a homework assignment and homework should be done by the person it is assinged to. You c...

Execute Stored procedure scripts in bulk
Hi, I have a folder containing about 150 stored procedures. Is there a way - through batch or command file to execute all of them at once..execute all files ending in .sql extension (inside a folder). Its not possible to list each SP name in a command file or execute each SP in management studio. please suggest. thanks > I have a folder containing about 150 stored procedures. Is there a way > - through batch or command file to execute all of them at > once..execute all files ending in .sql extension (inside a folder). A lowly Windows FOR command can help you here. The FOR comman...

Stored Procedure
Hello everyone, Against my better judgement I have been thrust into the duty of creating Crystal reports for my company. I have discovered rather quickly that I also need to become familar with SQL (creating views and stored procedures). Quick question. I have a stored procedure were I created a workfile. Several thousand records get inserted in this work file. Is it normally quicker to join all the tables I need for the insert or is it faster to get all the base records inserted into the workfile then do one or more updates to the workfile using the other tables that I didn't join. ...

Inserting Data using a Stored Procedure
In Microsoft SQL I can run the following command: insert into table execute proc1 to insert data into a table. Is there a comparable way to do this in DB2? I am not sure if we can directly call the procedure and insert its result set into a table in a single statement. Another way out is to to write a stored procedure for doing the same ..You can associate a cursor with stored procedure result set and hence fetch the data from the stored procedure result which can be inserted into the table . You can use ASSOCIATE RESULT SET LOCATORS and ASSOCIATE CURSOR for the same . Re...

Calling an Insert Stored Procedure in ASP
Ok, folks. Here's the deal. Basically, I have a Stored Procedure in Oracle that inserts data into a table. Simple enough. I want to call this sp from ASP, and here is the code: InsertQuery = "'" & serial_num & "','" & payment_type & "', " & "'" & contract_num & "', '" & delivery_task_order & "', '" & invoice_num & "', '" & invoice_date & "', '" & invoice_entered & "', " & ...

Calling a stored procedure from an insert statement
Can someone provide guidance on how I can call a stored procedure from a sql statement. We have a procedure which generates sequence numbers. I want to call this procedure to generate a value for an insert statement. Any help is greatly appreciated. On 25 Mar 2006 19:56:40 -0800, "bmaxed" <brodellis@gmail.com> wrote: >Can someone provide guidance on how I can call a stored procedure from >a sql statement. We have a procedure which generates sequence numbers. > I want to call this procedure to generate a value for an insert >statement. > >A...

Stored Procedure und insert on a variable tables
create procedure createKonto($myKonto integer, $einzahlung double) BEGIN DECLARE $t TEXT; IF ( NOT EXISTS ( select * from konto where kontoNr = $myKonto ) ) THEN SET $t = CONCAT('konto_' , CAST($myKonto AS CHAR)); INSERT INTO konto (kontoNr, kontoStand) VALUES( $myKonto,$einzahlung ); INSERT INTO $t (buhung, erfolg) VALUES($einzahlung, 1); END IF; ...

Stored Procedures: INSERT VALUES string as a parameter
How can I get this stored procedure to work? I want to pass all the INSERT parameters as one string. /*I want this to insert 2 records*/ DROP PROCEDURE IF EXISTS spTest$$ CREATE PROCEDURE spTest() BEGIN DECLARE pVal TEXT; SET pVal = "('a','b','c','d','e','f'), ('g','h','i','j','k','l')"; INSERT INTO test (fld1,fld2,fld3,fld4,fld5,fld6) VALUES pVal; END$$ /*I've tried prepared statements as well without success*/ DROP PROCEDURE IF EXISTS glTest$$ CREATE PRO...

Stored Procedure + sql server 2005 + insertion
hi..... can any body help me? i want to insert data into sql server(2005)database... i have a int value to pass...... how can i send it to the stored procedure ..... here IndID is the integer value but when i want to pass it... it is showing the follwing error: DBI::DatabaseError: Execute OLE error code:80040E07 in Microsoft OLE DB Provider for SQL Server Error converting data type varchar to int //// here also showing scalar variable must be declared if single quote taken off HRESULT error code:0x80020...

ADO: Can I insert a stored procedure in a transaction
Hi, Can I mix ADO-code (insert, update recordsets) and stored procedures in a transaction? Filip Sure - no problem. Just use an ADO Command object to execute the stored procedure, and make sure everything is using the same Connection instance that is handling the transaction. Of course, you need to watch if the stored procedures themselves are doing BEGIN TRANSACTION, etc., since this could confuse the issue. On Thu, 05 Feb 2004 20:39:31 GMT, "Filips Benoit" <benoit.filips@pandora.be> wrote: >Hi, > >Can I mix ADO-code (insert, update recordsets) and stored pro...

inconsistent order by using insert into in a stored procedure
hi there, i am using sql server 7. below is the stored procedure that is giving me grief. its purpose it two-fold, depending on how it is called: either to return a pageset (based on page number and page size), or to return IDs of previous and next records (based on current record id). the problem is, that the order in which records are inserted into the temp table is inconsistent, even though the calling statement and the order by is always the same: sometimes records are ordered correctly, by project_number, and sometimes the order is broken starting at some record (which is always the s...

Jdbc Sybase
All, I am using Java to call a SP which inserts data in data base and returns rowid of inserted row, and success or failure. When I call stored procedure from java, it gets executed and returns 0 as rowid and 1 as success message. It does not insert any data in the table. The java code calling stored porcedure is following connection = mySilverDB.getConnection(); connection.setAutoCommit(true); call= connection.prepareCall({call dbo.proc_name(?,?,?,?)}); call.setLong(1, this.reportID); call.registerOutParameter(2,Types.NUMERIC); call.registerOutParameter(3,...

Crystal Reports and Oracle Stored Procedure Insert statments
I'm assigned to improve performance on a stored procedure that is called by Crystal Reports. The existing PL/SQL block is large and contains many instances of two sub-queries. My plan was to run these repeated queries just one time, inserting the rows into two Global Temporary Tables. My code works until I put an insert statement in the stored procedure. The Crystal Report returns 'Failed to open a rowset. File <filename.rpt>. [On Cache/Page Server: <myServerName>.pageserver]'. When I comment out the insert statements and use the inline statements in the final sel...

Inserting data into Access form from SQL Stored Procedure
I am attempting to create an Access database which uses forms to enter data. The issue I am having is returning the query results from the Stored Procedure back in to the Access Form. tCetecM1CUST (SQL Table that contains the Customer Information) tAccountingDetail (SQL Table that contains the information in the form) frmAccountingEntry (Access form used to enter data) spGetCustomerInformation (Stored Procedure which returns data using variable CUSTOMER_NUMBER entered in the Access.) Scenario is this. Open form, Enter 'Job Number' and 'Customer Number', form uses 'AfterUp...

Problem with stored procedure and retrieving inserted identity value
Hello! I use a procedure to insert a new row into a table with an identity column. The procedure has an output parameter which gives me the inserted identity value. This worked well for a long time. Now the identity value is over 700.000 and I get errors whiles retrieving the inserted identitiy value. If I delete rows and reset the identity everything works well again. So I think it is a data type problem. My Procedure: create procedure InsertProduct @NEWID int output as begin set nocount on insert into PRODUCT(D_CREATED)values(getdate()+'') set nocount off select @NEWID = @@IDE...

Inserting a record using values from another Stored Procedure
Hello, I'm trying to accomplish 3 things with one stored procedure. I'm trying to search for a record in table X, use the outcome of that search to insert another record in table Y and then exec another stored procedure and use the outcome of that stored procedure to update the record in table Y. I have this stored procedure (stA) CREATE PROCEDURE procstA (@SSNum varchar(9) = NULL) AS SET NOCOUNT ON SELECT OType, Status, SSN, FName, LName FROM Customers WHERE (OType = 'D') AND (Status = 'Completed') AND (SSN = @SSNum) GO. Then, I need to create a ...

stored procedure: dinamically create / insert / drop table
Hi, during a procedure execution I want to dinamically create a table and insert rows in this table. execute immediate 'create table X1...'; insert into X1; when trying to compile the procedure the problem is that X1 table does not exist so I have to use dynamic SQL to do the INSERT . Is there any way to avoid this problem? thanks, Jon wrote: > Hi, > > during a procedure execution I want to dinamically create a table and insert > rows in this table. > > execute immediate 'create table X1...'; > insert into X1; > > when trying to compile the...

Cannot insert data when there is "select ... into..." in stored procedure
Hi All, I am doing database programming using VB.Net and Oracle DB 8.1.7. when there is command "select ...into..." in the following procedure, it doesn't insert anything and also no error return to the application. When I comment those command, it does insert records even they are null. What's wrong with my select statement? I can select value when I run the command in sqlplus. Anyone can have idea? Thanks in advanced. ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ PROCEDURE INSERT_TEMPLATE_DETAIL(P_TEMPLATE_CODE IN VARCHAR2, ...