| How to Batch Insert ? [message #45425] |
Sun, 14 March 2004 23:01  |
How to Batch Insert ?
Messages: 2
Registered: March 2004
|
Junior Member |
|
|
Is there any method,in which I can do the insert as a batch?
Now in my code, i am fetching data into a cursor and in a loop in a calling the insert statement ..
for l_lop in cur_emp
loop
Insert into EMP_MASTER(NAME,ID) values(l_lop.name,l_lop.id);
end loop;
commit;
This is becoming very slow.
Can you please suggest any method,in which I can do the insert as a batch or do the same operation very fast?
(Database :Oracle 8i)
|
|
|
|
|
|
| Re: How to Batch Insert ? [message #45430 is a reply to message #45425] |
Mon, 15 March 2004 00:47  |
Jitendra Agrawal
Messages: 71
Registered: December 2003
|
Member |
|
|
Hi,
Try this. It uses bulk binding (forall)
I have not compiled it.. so it may not run first time.. tweak a bit and you may get it right.
type IdTab is table of number(10) index by binary_integer;
type nameTab is table of varchar2(10) index by binary_integer;
ids IdTab;
names nameTab;
for l_lop in cur_emp
loop
ids(ids.count+1) := l_lop.id;
names(names.count+1) := l_lop.name;
if ( mod(ids.count,1000) = 0 )
then
forall i in 1 .. ids.count
Insert into EMP_MASTER(NAME,ID)
Values(ids(i),names(i));
ids := empty;
names := empty;
end if;
end loop;
if ( ids.count is not null )
then
forall i in 1 .. ids.count
Insert into EMP_MASTER(NAME,ID)
Values(ids(i),names(i));
end if;
commit;
Regards,
Jitendra Agrawal.
http://www.telemune.com/
|
|
|
|
| Re: How to Batch Insert ? [message #45433 is a reply to message #45425] |
Mon, 15 March 2004 08:00 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
Everone, please look at Franco's reply to the duplicate of this question. An INSERT INTO/SELECT FROM is the way to go here. Bulk binding is not going to be any faster than that.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
