Q
Problem solve Get help with specific problems with your technologies, process and projects.

Bulk inserts

Is there any thing like Bulk Insert in Oracle?

Bulk insert exists in Oracle and has for quite some time. Here's an example from the PL/SQL Users Guide and Reference:

In the example below, 5000 part numbers and names are loaded into index-by tables. Then, all table elements are inserted into a database table twice. First, they are inserted using a FOR loop, which completes in 32 seconds. Then, they are bulk-inserted using a FORALL statement, which completes in only 3 seconds.
SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));

Table created.

SQL> GET test.sql
 1  DECLARE
 2     TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
 3     TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
 4     pnums  NumTab;
 5     pnames NameTab;
 6     t1 NUMBER(5);
 7     t2 NUMBER(5);
 8     t3 NUMBER(5);
 9     PROCEDURE get_time (t OUT NUMBER) IS
10     BEGIN SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM dual; END;
11  BEGIN
12     FOR j IN 1..5000 LOOP  -- load index-by tables
13        pnums(j) := j;
14        pnames(j) := 'Part No. ' || TO_CHAR(j); 
15     END LOOP;
16     get_time(t1);
17     FOR i IN 1..5000 LOOP  -- use FOR loop
18        INSERT INTO parts VALUES (pnums(i), pnames(i));
19     END LOOP;
20     get_time(t2);
21     FORALL i IN 1..5000  -- use FORALL statement
22        INSERT INTO parts VALUES (pnums(i), pnames(i));
23     get_time(t3);
24     DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
25     DBMS_OUTPUT.PUT_LINE('---------------------');
26     DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR(t2 - t1));
27     DBMS_OUTPUT.PUT_LINE('FORALL:   ' || TO_CHAR(t3 - t2));
28* END;
SQL> /
Execution Time (secs)
---------------------
FOR loop: 32
FORALL:   3

PL/SQL procedure successfully completed.
To bulk-bind input collections, you use the FORALL statement. To bulk-bind output collections, you use the BULK COLLECT clause.

Here's an example of BULK COLLECT:

declare
	type n_table is table of number index by binary_integer;

	n1_tab n_table;
	n2_tab n_table;

	m_running_fetch		number := 0;

	cursor c1 is
	select n1,n2 from t1;

begin
	open c1;
	loop
		fetch c1 
		bulk collect into n1_tab, n2_tab limit 100;

		forall i in 1..c1%rowcount - m_running_fetch
		insert into t2 values (n1_tab(i),n2_tab(i));

		m_running_fetch := c1%rowcount;
		exit when c1%notfound;

	end loop;

	close c1;
end;
/
This example is courtesy of Jonathon Lewis. Notice the use of the limit option on the bulk collect fetch. This acts like a "throttle" and is shown for example purposes. If you don't use a limit, you'd fetch everything at once.

This is just one simple example. Have a look at this link for more examples. Also, I'd advise taking a look at the Oracle documentation (the PL/SQL Users Guide and Reference) on this topic.


Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close