You can inserting Multiple Rows by using Parameter Arrays:

You can use parameter arrays to fetch, update, insert, or delete
multiple rows in a table. Using parameter arrays for manipulating
multiple rows is more efficient than executing multiple statements that
operate on individual rows.

The following example demonstrates how the AddTable method of the
OraDatabase interface is used to create parameter arrays. The arrays are
then populated with values, and used as placeholders in the execution of
an INSERT statement that inserts two rows into the emp table.

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")
Set EmpDb = OO4OSession.OpenDatabase("Exampledb", "scott/tiger", 0)

'Creates parameter arrays for the empno, ename, job, and salary columns
EmpDb.Parameters.AddTable "EMPNO_ARRAY", ORAPARM_INPUT, ORATYPE_NUMBER,
2
EmpDb.Parameters.AddTable "ENAME_ARRAY", ORAPARM_INPUT,
ORATYPE_VARCHAR2, 2, 10
EmpDb.Parameters.AddTable "JOB_ARRAY", ORAPARM_INPUT, ORATYPE_VARCHAR2,
2, 9
EmpDb.Parameters.AddTable "MGR_ARRAY", ORAPARM_INPUT, ORATYPE_NUMBER, 2
EmpDb.Parameters.AddTable "DEPT_ARRAY", ORAPARM_INPUT, ORATYPE_VARCHAR2,
2, 10
Set EmpnoArray = EmpDb.Parameters("EMPNO_ARRAY")
Set EnameArray = EmpDb.Parameters("ENAME_ARRAY")
Set JobArray = EmpDb.Parameters("JOB_ARRAY")
Set MgrArray = EmpDb.Parameters("MGR_ARRAY")
Set DeptArray = EmpDb.Parameters("DEPT_ARRAY")

'Populate the arrays with values
EmpnoArray(0) = 1234
EnameArray(0) = "JORDAN"
JobArray(0) = "SALESMAN"
MgrArray(0) = 7839
DeptArray(0) = 30
EmpnoArray(1) = 1235
EnameArray(1) = "YOUNG"
JobArray(1) = "SALESMAN"
MgrArray(1) = 7839
DeptArray(1) = 30

'Insert two rows
EmpDb.ExecuteSQL ("INSERT INTO emp (empno, ename, job, mgr, deptno)
VALUES" & _
"(:EMPNO_ARRAY,:ENAME_ARRAY, :JOB_ARRAY,:MGR_ARRAY,
:DEPT_ARRAY)")

Larry Wang

-----Original Message-----
From: Dave
[mailto:oracledba-ezmlmshield-x64263233.[Email address protected]
Sent: Monday, October 08, 2007 4:47 PM
To: LazyDBA Discussion
Subject: Creating multiple rows at once

This one will be easy for you guys.
How do I insert a bunch of data all at once?
Instead of this:

INSERT INTO table name
VALUES ('value this', 'value that');

[and then do it again for the next row]

INSERT INTO table name
VALUES ('value this', 'value that');

How do I just keep going before executing?
Like this:

INSERT INTO table name
VALUES ('value this', 'value that') ('value this', 'value that')
('value this', 'value that') ('value this', 'value that')
('value this', 'value that');




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



**********************************************************************
CONFIDENTIALITY NOTICE: This email and attachments, if any, are the sole property
of One Lambda, Inc. and may contain information that is confidential, proprietary,
privileged, or otherwise prohibited by law from disclosure or re-discloasure. This
information is intended solely for the individuals or entities to whom this e-mail or
attachments are addressed. If you have received this e-mail in error, you are
prohibited from using, copying, saving or disclosing this information to anyone else.
Please destroy the message and any attachments immediately and notify the sender
by return e-mail. This footnote also confirms that this e-mail message has been swept
by MIMEsweeper for the presence of computer viruses
**********************************************************************


Oracle LazyDBA home page