Databases Reference
In-Depth Information
8.
Truncate the
MY_CUSTOMERS
table to empty it:
TRUNCATE TABLE sh.MY_CUSTOMERS;
9.
Populate the
MY_CUSTOMERS
table by retrieving the data from the
CUSTOMERS
table with a
BULK
COLLECT
statement and using a
FORALL
statement to insert
the records in batches of 200:
SET TIMING ON
DECLARE
TAB_ID T_ID;
TAB_NAME T_NAME;
CURSOR MY_CURSOR IS SELECT CUST_ID, CUST_FIRST_NAME FROM
CUSTOMERS;
BEGIN
OPEN MY_CURSOR;
LOOP
FETCH MY_CURSOR BULK COLLECT INTO TAB_ID, TAB_NAME LIMIT 200;
EXIT WHEN TAB_ID.COUNT = 0;
FORALL J IN TAB_ID.FIRST..TAB_ID.LAST
INSERT INTO sh.MY_CUSTOMERS (CUST_ID, CUST_FIRST_NAME)
VALUES (TAB_ID(J), TAB_NAME(J));
END LOOP;
CLOSE MY_CURSOR;
END;
/
SET TIMING OFF
10. Clean the schema by dropping the objects used in this recipe:
DROP TABLE sh.MY_CUSTOMERS;
DROP TYPE sh.T_ID;
DROP TYPE sh.T_NAME;
How it works...
In this recipe, we have used three methods to insert records (huge amounts of data) in the
MY_CUSTOMERS
table, by selecting rows from the
CUSTOMERS
table.
In step 3, we have used a simple
FOR
loop that reads records from
CUSTOMERS
and inserts
them in
MY_CUSTOMERS
, one row at a time.
