Databases Reference
In-Depth Information
6.
Insert the data using a simple SQL statement:
INSERT /*+ APPEND */ INTO sh.MY_SALES (cust_id, prod_id)
SELECT cust_id, prod_id
FROM sh.sales
WHERE time_id between TO_DATE('20010701', 'YYYYMMDD')
AND TO_DATE('20011231', 'YYYYMMDD');
7.
After our tests, drop the used table:
SET TIMING OFF
DROP TABLE sh.MY_SALES;
How it works...
In the first steps, we have created an empty table called
MY_SALES
to test different methods
to insert data.
The first example, in step 4, uses a procedural approach. We open the
curs_c1
cursor to
loop through the
SALES
table, selecting the rows we want to insert in the
MY_SALES
table;
the insert is done using a row-by-row loop.
The results obtained are shown in the following screenshot:
The second example, in step 5, again uses a PL/SQL block. This time we use two arrays,
customers_list
and
products_list
, to store data temporarily; one is used for the
cust_id
and the other for the
prod_id
, as defined in the
DECLARE
section of the code.